Database Reference
In-Depth Information
PRIMARY KEY ("DEPTNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255
TABLESPACE "USERS" ENABLE
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
TABLESPACE "USERS"
NESTED TABLE "EMPS" STORE AS "EMPS_NT"
(( CONSTRAINT "EMPS_EMPNO_UNIQUE" UNIQUE ("EMPNO")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
TABLESPACE "USERS" ENABLE)
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
TABLESPACE "USERS" ) RETURN AS VALUE
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 co-located 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_ID
s 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
EODA@ORA12CR1> CREATE TABLE "EODA"."DEPT_AND_EMP"
2 ("DEPTNO" NUMBER(2, 0),
3 "DNAME" VARCHAR2(14),
4 "LOC" VARCHAR2(13),
5 "EMPS" "EMP_TAB_TYPE")
6 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
7 TABLESPACE "USERS"
8 NESTED TABLE "EMPS"
9 STORE AS "EMPS_NT"
10 ((empno NOT NULL, unique (empno), primary key(nested_table_id,empno))
11 organization index compress 1 )
12 RETURN AS VALUE;
Table created.
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
.
