Re: Preventing Nested Table Full Access

From: Job Miller <jobmiller_at_yahoo.com>
Date: Mon, 1 Apr 2013 09:07:28 -0700 (PDT)
Message-ID: <1364832448.24464.YahooMailNeo_at_web126106.mail.ne1.yahoo.com>

Can you flatten out this mess with traditional parent/child, primary key/foreign key tables.  it will make searching for what "object" you want much easier. if the client really wants the "object", create an O/R view on top of the relational stuff.  That way you get convenient well understood relational query responses with traditional CBO type mechanics, but the requesting application gets its result set all wrapped up in the pretty "object" bow it was looking for.

I'll refer back to an old article from Asktom.oracle.com

Tom said:

"I'll never use a nested table in a CREATE TABLE statement.  You spend all of your time UN-NESTING them to make them useful again!"

He went on to say:

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

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:  http://asktom.oracle.com/pls/asktom/f?p0:11:0::::P11_QUESTION_ID:8135488196597

--
http://www.freelists.org/webpage/oracle-l

Received on Mon Apr 01 2013 - 18:07:28 CEST