Re: Preventing Nested Table Full Access
Date: Mon, 01 Apr 2013 10:57:31 -0600
Message-ID: <5159BC7B.2070903_at_evdbt.com>
Anupam,
I strongly endorse Job's recommendation to use object-relational (O/R) views rather than O/R data structures such as nested tables. In other words, create a relational structure in the database, but expose it to the application through the object view.
This is well documented, as it has been around for about 16 years since Oracle8 v8.0 was introduced in 1997, in Chapter 6 in the Oracle Database "Object-Relational Developer's Guide" online at "http://docs.oracle.com/cd/E18283_01/appdev.112/e11822/adobjvew.htm <http://docs.oracle.com/cd/E18283_01/appdev.112/e11822/adobjvew.htm#i434776>".
Specifically, there are examples and explanations in the following sections of that chapter...
- "Nesting Objects in Object Views" at "http://docs.oracle.com/cd/E18283_01/appdev.112/e11822/adobjvew.htm#i434727"
- "Using Nested Tables and Varrays in Object Views" at "http://docs.oracle.com/cd/E18283_01/appdev.112/e11822/adobjvew.htm#i434776"
- "Updating Object Views" at "http://docs.oracle.com/cd/E18283_01/appdev.112/e11822/adobjvew.htm#i436241 <http://docs.oracle.com/cd/E18283_01/appdev.112/e11822/adobjvew.htm#i434776>"
Hope this helps...
-Tim
On 4/1/2013 10:07 AM, Job Miller wrote:
> 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
>
>
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Apr 01 2013 - 18:57:31 CEST