Oracle FAQ Your Portal to the Oracle Knowledge Grid

Home -> Community -> Mailing Lists -> Oracle-L -> Re: use of nested tables to resolve many to many relationships

Re: use of nested tables to resolve many to many relationships

From: Job Miller <>
Date: Wed, 7 Nov 2007 17:20:39 -0800 (PST)
Message-ID: <>

it was just for you, but since it went out to everyone i'll elaborate.... no, i'll let asktom elaborate for you.
1) they are parent child tables in disguise but ones that add:

a 16 byte raw with a unique constraint on the parent table.  Most likely you ALREADY HAVE a primary 
key on the parent table and most likely it is smaller.

a 16 byte raw that you need to index on the child (not auto-indexed, you need to know to do it).  
This is the foreign key and is hidden from you.

The inability to put many types of constraints on the nested table..

They are simple parent/child tables - except you lose the ability to access the child table 

2) you are NOT storing anything in a "single row".  Physically they are a parent child table pair, 
nothing more, nothing less

If you have my book "Expert one on one Oracle" - I write about them in there, describe their 
implementation and talk about when I would use them. 


<quote from Tom K.>
i use object relational extensions as a tool to make PLSQL more powerful.  I have yet to use 
these features to store data -- although as a method to store an XML document in a "structured" 
format, they might have some appeal


----- Original Message ----
From: "Stephens, Chris" <>
Sent: Wednesday, November 7, 2007 9:37:56 AM
Subject: FW: use of nested tables to resolve many to many relationships

I donąt think this was intended for just me.
From: Job Miller [] 
Sent: Wednesday, November 07, 2007 7:33 AM
To: Stephens, Chris
Subject: Re: use of nested tables to resolve many to many relationships
stay away..  you'll be quite thankful.   it is just relational anyway, and accessing them from clients and via sql becomes much more complex.    you can use views to make relational data look like nested data if you really want a result set to come back that contains a row that has collection of child elements. (like a nested table)

This message is intended for the use of the individual or entity to which it is addressed and may contain information that is privileged,
confidential and exempt from disclosure under applicable law. If the reader of this message is not the intended recipient or the employee or agent responsible for delivering this message to the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited. If you have received this
communication in error, please notify us immediately by email reply..

Do You Yahoo!?
Tired of spam?  Yahoo! Mail has the best spam protection around 
Received on Wed Nov 07 2007 - 19:20:39 CST

Original text of this message