Using Oracle nested tables Donald K. Burleson Oracle Tips

Using Oracle nested tables

Using the Oracle nested table structure, subordinate data items can be directly linked to the base table by using Oracle's newest construct, the object ID (called an “OID”). One of the remarkable extensions of the Oracle database is the ability to reference Oracle objects directly by using pointers as opposed to relational table joins. Proponents of the object-oriented database approach often criticize standard relational databases because of the requirement to reassemble an object every time it is referenced.

In this example, an Oracle nested table is used to represent a repeating group for previous addresses. Whereas a person is likely to have a small number of previous employers, most people have a larger number of previous addresses, and the Oracle nested tables allows repeating groups to be linked to the employee with pointers.

First, we create a Oracle TYPE, using a full_mailing_address_type abstract data type:

create type

   full_mailing_address_type

(

   st_addr       varchar2(30),

   city_name   varchar2(20),

   state            char(2),

   zip              char(5)

);

create type

   prev_addrs

as object

(

   prior_address
   full_mailing_address_type

);

Next, we create the nested object:

create type

   nested_address

as table of

   prev_addrs;

Now, we create the parent table with the nested table.

create table

   emp

(

   last_name            char(40),

   current_address   full_mailing_address_type,

   prev_address       nested_address 

)

nested table

   prev_address

store as

   nested_prev_address return as locator;

That’s all there is to it. The nested_prev_address subordinate table can be indexed just like any other Oracle table. Also, notice the use of the return as locator SQL syntax. In many cases, returning the entire Oracle nested table at query time can be time-consuming.

The locator enables Oracle to use the pointer structures to dereference pointers to the location of the Oracle nested rows. A pointer dereference happens when you take a pointer to an object and ask the program to display the data the pointer is pointing to.

If you like Oracle tuning, you might enjoy my latest book “Oracle Tuning: The Definitive Reference” by Rampant TechPress.  It’s only $41.95 (I don’t think it is right to charge a fortune for books!) and you can buy it right now at this link:

Copyright © 1996 -  2009 by Burleson Enterprises, Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.