Database Tables - Expert Oracle Database Architecture - page 412

we know that Oracle really creates a structure like the one shown in Figure 10-11 .

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-11. Nested table physical implementation

The code created two real tables. The table we asked to have is there, but it has an extra hidden column (we'll
have one extra hidden column by default for each nested table column in a table). It also created a unique constraint
on this hidden column. Oracle created the nested table, EMPS_NT , for us. This table has two hidden columns, one
of which, SYS_NC_ROWINFO$ , is not really a column but a virtual column that returns all of the scalar elements as an
object. The other is the foreign key called NESTED_TABLE_ID , which can be joined back to the parent table. Notice
the lack of an index on this column. Finally, Oracle added an index on the DEPTNO column in the DEPT_AND_EMP table
to enforce the primary key. So, we asked for a table and got a lot more than we bargained for. If you look at it, it is a
lot like what you might create for a parent/child relationship, but you would have used the existing primary key on
DEPTNO as the foreign key in EMPS_NT instead of generating a surrogate RAW(16) key.
If we look at the DBMS_METADATA.GET_DDL dump of our nested table example, we see the following:

EODA@ORA12CR1> begin
2 dbms_metadata.set_transform_param
3 ( DBMS_METADATA.SESSION_TRANSFORM, 'STORAGE', false );
4 end;
5 /
PL/SQL procedure successfully completed.

EODA@ORA12CR1> select dbms_metadata.get_ddl( 'TABLE', 'DEPT_AND_EMP' ) from dual;

DBMS_METADATA.GET_DDL('TABLE','DEPT_AND_EMP')
--------------------------------------------------------------------------------

CREATE TABLE "EODA"."DEPT_AND_EMP"
( "DEPTNO" NUMBER(2,0),
"DNAME" VARCHAR2(14),
"LOC" VARCHAR2(13),
"EMPS" "EODA"."EMP_TAB_TYPE",