Database Tables - Expert Oracle Database Architecture - page 410

These two columns don't even show up. They are part of the hidden implementation of nested tables. The
NESTED_TABLE_ID is really a foreign key to the parent table DEPT_AND_EMP. DEPT_AND_EMP actually has a hidden
column in it that is used to join to EMPS_NT . The SYS_NC_ROWINFO$ column is a magic column; it is more of a function
than a column. The nested table here is really an object table (it is made of an object type), and SYS_NC_ROWINFO$ is
the internal way Oracle references the row as an object, instead of referencing each of the scalar columns. Under the
covers, all Oracle has done for us is implement a parent/child table with system-generated primary and foreign keys.
If we dig a little deeper, we can query the real data dictionary to see all of the columns in the DEPT_AND_EMP table:
EODA@ORA12CR1> select name
2 from sys.col$
3 where obj# = ( select object_id
4 from dba_objects
5 where object_name = 'DEPT_AND_EMP'
6 and owner = 'EODA' )
7 /
NAME
------------------------------
DEPTNO
DNAME
EMPS
LOC
SYS_NC0000400005$
Selecting this column out from the nested table, we'll see something like this:
EODA@ORA12CR1> select SYS_NC0000400005$ from dept_and_emp;
SYS_NC0000400005$
--------------------------------
EF6CDA23E32D315AE043B7D04F0AA620
EF6CDA23E32E315AE043B7D04F0AA620
EF6CDA23E32F315AE043B7D04F0AA620
EF6CDA23E330315AE043B7D04F0AA620
The weird-looking column name, SYS_NC0000400005$ , is the system-generated key placed into the DEPT_AND_
EMP table. If we dig even deeper, we will find that Oracle has placed a unique index on this column. Unfortunately,
however, it neglected to index the NESTED_TABLE_ID in EMPS_NT . This column really needs to be indexed, as we are
always joining from DEPT_AND_EMP to EMPS_NT . This is an important thing to remember about nested tables if you use
them with all of the defaults as just done: always index the NESTED_TABLE_ID in the nested tables!
I've gotten off track, though, at this point—I was talking about how to treat the nested table as if it were a real
table. The NESTED_TABLE_GET_REFS hint does that for us. We can use the hint like this:
EODA@ORA12CR1> select /*+ nested_table_get_refs */ empno, ename
2 from emps_nt where ename like '%A%';
EMPNO ENAME
---------- ----------
7782 CLARK
7876 ADAMS
7499 ALLEN
7521 WARD