Re: Preventing Nested Table Full Access
- From: Job Miller <jobmiller@xxxxxxxxx>
- To: "andrew.kerber@xxxxxxxxx" <andrew.kerber@xxxxxxxxx>, "my.oralce@xxxxxxxxx" <my.oralce@xxxxxxxxx>
- Date: Mon, 1 Apr 2013 09:07:28 -0700 (PDT)
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
--
//www.freelists.org/webpage/oracle-l
Other related posts:
