Database Reference
In-Depth Information
Dept_and_Emp
DEPTNO
NUMBER(2)
SYS_0001788
VARCHAR2(14)
DNAME
LOC
VARCHAR2(13)
RAW(16)
SYS_NC0000400005S
Emps_NT
SYS_0001787
SYS_NC_ROWINFOS
NESTED_TABLE_ID
EMPNO
ENAME
JOB
MGR
HIREDATE
SAL
COMM
RAW(16)
NUMBER(4)
VARCHAR2(10)
VARCHAR2(9)
NUMBER(4)
DATE
NUMBER(7, 2)
NUMBER(7, 2)
SYS_0001789
Figure 10-12.
Nested table implemented as an IOT
Where the
EMPS_NT
is an IOT using compression, it should take less storage than the original default nested table
and
it has the index we badly need.
Nested Tables Wrap-up
I do not use nested tables as a permanent storage mechanism myself, for the following reasons:
RAW(16)
columns that are added. Both the parent
and child table will have this extra column. The parent table will have an extra 16-byte RAW
for each nested table column it has. Since the parent table typically already has a primary
key (
DEPTNO
in my examples), it makes sense to use this key in the child tables, not a system-
generated key.
•
The unnecessary storage overhead of the
•
The unnecessary overhead of the additional unique constraint on the parent table, when it
typically already has a unique constraint.
•
The nested table is not easily used by itself, without using unsupported constructs
(
NESTED_TABLE_GET_REFS
). It can be un-nested for queries, but not mass updates. I have yet to
find a table in real life that isn't queried “by itself.”
I do use nested tables heavily as a programming construct
and in views. This is where I believe they are in their
element. As a storage mechanism, I much prefer creating the parent/child tables myself. After creating the parent/
child tables, we can, in fact, create a view that makes it appear as if we had a real nested table. That is, we can achieve
all of the advantages of the nested table construct without incurring the overhead.
If you do use a nested table as a storage mechanism, be sure to make it an IOT to avoid the overhead of an index
on the
NESTED_TABLE_ID
and the nested table itself. See the previous section on IOTs for advice on setting them up
with overflow segments and other options. If you do not use an IOT, make sure to create an index on the
NESTED_TABLE_ID
column in the nested table to avoid full scanning it to find the child rows.
