Thursday, 12 April 2012

What are Nested Tables ?

"Nested Table", as the name suggests is table within a table. A normal Heap Organized Table , if has a column whose datatype is table like object, then such column is called as "column object" and since it being a table may store "unordered" set of data elements, all of the same type as the datatype of the column, hence it is very much like a table within the table and is called as "Nested Table". Actually creating nested table is sort of going out of bounds that allows us to create a relationship within a table that could have been easily done with normal two separate "parent" and "child" like relational tables. At least some of the authors like Thomas Kyte believe it so and do not much encourage to use nested tables in this way.

"Nested Table", as a part of Object Relational Extension, in Oracle, has been provided as one of the type of "collection objects" (read array but somewhat different in some sense), the other being "varray". The only points of differences between these two types of collections are, the former is virtually unbounded (with no upper limit to number of elements it may contain) whereas latter is bounded and also while the former may be manipulated easily by "Extended" SQL, the latter may not and requires PL/SQL program to manipulate. So the discussion about Nested Table is considered enough to give a fair idea about the varrays.

As has been said above, the Nested Table is very much similar to a child table in a traditional parent/child pair in the relational model but just has been facilitated with being defined as an object data type to be used to create a column in the normal table. So in a way it gives an illusion that each row in parent table has its own child table. Visually the structure could somewhat resemble as follows -

Table deptemp :
deptno dname loc empt

empno ename job salary darate itxrate xemplim
10 acct NY 1 Peter manager 3000 120 10 24000

2 George clerk 800 140 5 6000
... ... .... .... .... .... ....
20 research CA 15 Scott analyst 2900 110 9 22000

25 Julia analyst 3200 120 10 23000
.... .... .... .... .... .... ....

A Live Example -

We shall consider the same object (empitax) that we created in our post about Object Table and use it to create our Nested Table type collection (emptaxtab) and its column (empt) in a "deptemp" table.

Step 1 -
Of course creating the object type empitax which we did in out post about Object Table. (not to be repeated here)

Step 2 -
We shall create "Nested Table" type collection

create or replace type emptaxtab as table of empitax;

Step 3 -
Creating the table "deptemp"

create table deptemp (deptno number(3) primary key,
                      dname  varchar2(15),
                      loc    varchar2(2),
                      empt   emptaxtab)
nested table empt store as empnest;

alter table empnest add constraint u_empnest unique (empno); 

Observe here that we're creating the column "empt" as type "emptaxtab" of nested table type collection we created in step 2. We are adding "unique" constraint in the statement "alter table ..." to force the "empno" to be unique. The nested table however does not allow to add any foreign key constraint, even the one to refer to its own unique column. The clause "nested table ... store as ..." has been explained at the bottom of this post.

Step 4 -
Data manipulation -
We may insert the rows in this table as -

insert into deptemp

In this same way we may put rows for all the departments and their employees. (observe the constructors "emptaxtab" and "empitax" being called. "constructors" are functions used for initializing (read providing) values for object attributes)

Now suppose we have recruited one more employee in deptno 10, then to add such employee row we require to use an operator "the" or "table" as given below -

insert into the(select empt from deptemp where deptno = 10)
values (5,'Bobby','accountant',1200,130,5,9000);

Now suppose we want to change salary of Peter working in department 10 from 3000 to 4000 then

update the (select empt from deptemp where deptno = 10)
set salary = 4000 
where ename = 'Peter';

Now if George leaves our company, so to delete his row -

delete from the(select empt from deptemp where deptno = 10)
where ename = 'George';

The "Table" or "the" operator (in fact a function) may be used to list the data in an un-nest way (similar to relational table join) -

select d.deptno, d.dname, d.loc, e.*
from deptemp d, table(d.empt) e;

Nested Table storage -

Recall the clause "nested table ... store as ...". Actually it implicitly did create a real physical table "empnest" apart from our table "deptemp" and stored it separately. Also look at each "select" statement in "the" or "table" operator. The predicate contains only an equality condition, "select empt from deptemp where deptno = 10". This is virtually selecting the "nested table" for one row in the table, here for department 10. And it must return at least one row or it will result into error "ORA-22908:reference to NULL object value". If this same predicate (for example deptno > 10) returns multiple nested tables, it will return error "ORA-01427:Single row subquery returns more than one row".

Nested Table Limitations -

So we may conclude - Oracle considers each row in "deptemp" table to point to a "table" (a nested table virtually only meant for it) and not a set of rows from another table as it happens in parent/child like relational tables. In relational model there may be set of rows in the child table for one parent row. This basic difference has some limitations in the use of nested tables.

The most important limitation is that we can not access the nested table directly, it has to be accessed through the main table (deptemp). So it is not easily possible to answer queries like "what department does peter work in". Also "how do you transfer an employee working in department 10 to department 30" in nested table? In relational table we just may update the "deptno" (a reference column in emp table to point to deptno column of dept table) column of the emp table from 10 to 30.

No comments:

Post a Comment