The nested really foreign key the parent table dept

Chapter 10 ■ Database tables

EODA@ORA12CR1> select d.dname, e.empno, ename, deptno 2 from dept_and_emp d, table(d.emps) e
3 where d.deptno in ( 10, 20 );

Where it will come in most handy is if you ever need to mass update the nested table (remember, you would have to do that through the DEPT table with a join). There is an underdocumented hint (it is mentioned briefly and not fully documented), NESTED_TABLE_GET_REFS, which is used by various tools (including the deprecated EXP and IMP utilities) to deal with nested tables. It is also a way to see a little more about the physical structure of the nested tables.

If you use this hint, you can query to get some “magical” results. The following query is what EXP (a data unload utility) uses to extract the data from this nested table:

EODA@ORA12CR1> desc emps_nt
Name Null? Type
----------------------------- -------- -------------------- EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
MGR NUMBER(4)
HIREDATE DATE
SAL NUMBER(7,2)
COMM NUMBER(7,2)

413

NAME
------------------------------
DEPTNO
DNAME
EMPS
LOC
SYS_NC0000400005$

Selecting this column out from the nested table, we’ll see something 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