Database Reference
In-Depth Information
When considering using this structure, you should employ the same considerations from the hash cluster
section, in addition to the constraint that the data should arrive sorted for each key value over time.
Nested Tables
Nested tables are part of the object-relational extensions to Oracle. A nested table, one of the two collection types in
Oracle, is very similar to a child table in a traditional parent/child table pair in the relational model. It is an unordered
set of data elements, all of the same data type, which could be either a built-in data type or an object data type. It goes
one step further, however, since it is designed to give the illusion that each row in the parent table has its
own
child
table. If there are 100 rows in the parent table, then there are
virtually
100 nested tables. Physically, there is only the
single parent and the single child table. There are large syntactic and semantic differences between nested tables and
parent/child tables as well, and we'll look at those in this section.
There are two ways to use nested tables. One is in your PL/SQL code as a way to extend the PL/SQL language.
The other is as a physical storage mechanism for persistent storage of collections. I use them in PL/SQL all of the time,
but I have never used them as a permanent storage mechanism.
In this section, I'll briefly introduce the syntax to create, query, and modify nested tables. Then we'll look at some
of the implementation details and what is important to know about how Oracle really stores nested tables.
Nested Tables Syntax
The creation of a table with a nested table is fairly straightforward—it is the syntax for manipulating them that gets a
little complex. Let's use the simple
EMP
and
DEPT
tables to demonstrate. We're familiar with that little data model that
is implemented relationally as follows
EODA@ORA12CR1> create table dept
2 (deptno number(2) primary key,
3 dname varchar2(14),
4 loc varchar2(13)
5 );
Table created.
EODA@ORA12CR1> create table emp
2 (empno number(4) primary key,
3 ename varchar2(10),
4 job varchar2(9),
5 mgr number(4) references emp,
6 hiredate date,
7 sal number(7, 2),
8 comm number(7, 2),
9 deptno number(2) references dept
10 );
Table created.
with primary and foreign keys. We'll do the equivalent implementation using a nested table for the
EMP
table:
EODA@ORA12CR1> create or replace type emp_type
2 as object
3 (empno number(4),
4 ename varchar2(10),
5 job varchar2(9),
