We have already seen some of the storage of the nested table structure. In this section, we’ll take an in-depth look at the structure created by Oracle by default and what control we have over it. Working with the same CREATE statement as before
$ sqlplus eoda/foo@PDB1
SQL> create table dept_and_emp(deptno number(2) primary key,dname varchar2(14),loc varchar2(13),emps emp_tab_type)nested table emps store as emps_nt; Table created.
SQL> alter table emps_nt add constraint emps_empno_unique unique(empno); Table altered.
we know that Oracle really creates a structure like the one shown in Figure 10-11.
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:
SQL> begindbms_metadata.set_transform_param( DBMS_METADATA.SESSION_TRANSFORM, ‘STORAGE’, false );end;/
The only new thing here so far is the RETURN AS VALUE clause. It is used to describe how the nested table is returned to a client application. By default, Oracle will return the nested table by value to the client; the actual data will be transmitted with each row. This can also be set to RETURN AS LOCATOR, meaning the client will get a pointer to the data, not the data itself. If—and only if—the client dereferences this pointer will the data be transmitted to it. So, if you believe the client will typically not look at the rows of a nested table for each parent row, you can return a locator instead of the values, saving on the network round trips. For example, if you have a client application that displays the lists of departments and when the user double-clicks a department it shows the employee information, you may consider using the locator. This is because the details are usually not looked at—that is the exception, not the rule.
So, what else can we do with the nested table? First, the NESTED_TABLE_ID column must be indexed. Since we always access the nested table from the parent to the child, we really need that index. We can index that column using CREATE INDEX, but a better solution is to use an IOT to store the nested table. The nested table is another perfect example of what an IOT is excellent for. It will physically store the child rows colocated by NESTED_TABLE_ID (so retrieving the table is done with less physical I/O). It will remove the need for the redundant index on the RAW(16) column. Going one step further, since the NESTED_TABLE_ID will be the leading column in the IOT’s primary key, we should also incorporate index key compression to suppress the redundant NESTED_TABLE_IDs that would be there otherwise. In addition, we can incorporate our UNIQUE and NOT NULL constraint on the EMPNO column into the CREATE TABLE command. Therefore, if we take the preceding CREATE TABLE statement and modify it slightly
we now get the following set of objects. Instead of having a conventional table EMPS_NT, we have an IOT EMPS_NT as signified by the index structure overlaid on the table in Figure 10-12.
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.