From: Tom Kyte

To: Fabian Pascal

Date: 29 Apr 2005

 

Yes, I read your paper [Pointing Backwards] actually - before you sent it to me. A friend of my from the Netherlands had read it and told me about it. He had the DB/M dutch magazine.

 

Funny thing is—Burleson even got the technical details on the implementation all wrong. When he states:

 

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).

 

that is so wrong—so very wrong. It shows a lack of understanding of the implementation.

 

Nested tables in Oracle are physically implemented using a master/detail. When you create a nested table, we silently and auto-magically add a 16 byte RAW unique key to the parent (want to talk overhead).

 

We create a child table with an un-indexed foreign key to this parent (and woe are you if you forget to index it). This foreign key is that 16 byte raw.

 

So, you basically have a weak entity (of which I'm hard pressed to find a real world example of a true weak entity that we never query standalone)—a table you cannot query EXCEPT in the context of the parent.

 

And, we join to it—you can even see the join happen if you trace. It is a parent/child pair of tables. The use of a nested table IMPLIES you will be putting the stuff back together, you do have to reassemble.

 

And an OID has nothing to do with them whatsoever. Nothing.

 

He got nested tables totally wrong. He talks about object IDs, and they are never used with nested tables. He missed the point of them entirely.

 

I myself hate nested tables and object tables. I like object TYPES as columns for some things (XMLtype, spatial, special purpose things), but nested tables and object tables are not allowed in my database. Lots of magic happens with them (and they are all relational tables 100% under the covers, the object stuff in Oracle is just a layer on top of tables, nothing you cannot code yourself much more efficiently).

 

In EXPERT ONE ON ONE ORACLE (the very chapter I'm working on the update of today in fact) I had this to say about them:

 

I do not use nested tables as a permanent storage mechanism myself, and this is for the following reasons:

 

·   The overhead of the RAW(16) columns that are added. Both the parent and child table will have this extra column. The parent table will have an extra 16 byte RAW for each nested table column it has. Since the parent table typically already has a primary key (DEPTNO in my examples) it would make sense to use this in the child tables, not a system generated key.

 

·   The overhead of the unique constraint on the parent table, when it already typically has a unique constraint.

 

·   The nested table is not easily used by itself, without using unsupported constructs (NESTED_TABLE_GET_REFS). It can be un-nested for queries but not mass updates.

 

I do use nested tables heavily as a programming construct and in views. This is where I believe they are in their element and in Chapter 20 on Using Object Relational Features we see how to exploit them in this fashion. As a storage mechanism, I would much prefer creating the parent/child tables myself. After creating the parent/child tables we can in fact create a view that makes it appear as if we had a real nested table. That is, we can achieve all of the advantages of the nested table construct without incurring the overhead. Again in Chapter 20 Using Object Relational Features we'll take a detailed look at how to accomplish this.

 

If you do use them as a storage mechanism, be sure to make the nested table an index organized table to avoid the overhead of an index on the NESTED_TABLE_ID and the nested table itself. See the section above on IOTs for advice on setting them up with overflow segments and other options. If you do not use an IOT, make sure then to create an index on the NESTED_TABLE_ID column in the nested table to avoid full scanning it to find the child rows.

 

 

From: Fabian Pascal

To: Tom Kyte

 

When somebody is ignorant in one area, chances is he's ignorant in other areas too. Can't be trusted.

 

Real nested tables are relation-valued domains/attributes [RVD/RVA]. There is no way Oracle implemented that, and I knew it without the details you're providing. Not possible.

 

As to objects, well, that's a losing propositions all around. It's always funny magic.

 

 

Ed. Comment:  Even if Oracle had implemented true nested relations, we have demonstrated in What First Normal Form Really Means and What First Normal Form Means Not that they complicate the language, but offer no advantage that justify the complications, beyond convenience in some rare special cases. That is precisely what Codd realized: in his first 1969 paper he thought of supporting them in the relational model, but in his second 1970 paper he normalized them away.

 

It is also fair to say that non-nested tables in Oracle (and SQL systems in general) are not relational, and while users can enforce relational fidelity themselves (by declaring keys, and disallowing NULLs), they cannot guarantee the relational fidelity of the DBMS itself, which was designed to accept and expect both, with poorly defined, or flat wrong treatments.

 

 

Posted 6/24/05