Re: foundations of relational theory? - some references for the truly starving

Mike Preece wrote:
Large snip

>>>The basis in facts are very simple: Nested Relations offer no extra 
>>>expressive power, and plenty of complications.

Because somebody from your side said that Pick/MV is pretty much the same as nested relation, on which there is a handful of scientific literature, whereas on Pick/MV there's no basic article detailing the model.

> Oracle has "Nested Tables"
> (http://www.dba-oracle.com/art_9i_data_model.htm). They have *pointers* to
> the location of the nested rows. This is *not* Pick/MV.
>

I think the reference is not the greatest. In any case nested table can be implemented "in situ" also (see Oracle 8i/9i complete reference, either in book or online).

There was a conscious decision of the guy who wrote the article to store it otherwise:

    store as nested_prev_address return as locator;

Which means, store in a separate table extent, put a table of pointers in the master table. If he hadn't specified this option the default would be to store them in situ.

The guy mentions in the article that he did it for performance consideration.

In any case in Oracle you INSERT/QUERY the records in nested table declaratively, i.e. they have the keyword named THE, which is roughly equivalent to the UNNEST operator in nested relations theory.

The same applies to Pick or any MV database, when you increase the size of a record you decrease the performance of all basic operations on the table. So if the master table is accessed/updated more frequently then accesses for the information in the two of them combined there's not a good case to cluster the parent/children records (or whatever your nomenclature is) together.

> In Pick the nested rows don't need pointers - they are in-situ. The item,
> or record, contains all of the related data - hence we get it all in a
> single read. No need to "dereference" anything. Not such a "plenty of
> complications".
>

The "complications are with regards to integrity constraints, normalization theory and query optimization.
>

>>>It is you who should give me a good reference -- not marketing idiocies 
>>>about how "joins are difficult to compute" --, that you consider 
>>>relevant for showing the difference. And we'll see.

>>>>Regards,
>>>>Mike.

>>>Regards,
>>>Costin

So I'm still curious for the exact difference that nesting tables buys me.

What the hell, give us a simple example, with CUSTOMER, ORDER ORDERLINES, PRODUCTS. I believe you'd nest ORDERLINES into ORDERS, so what's the big deal about it ? What's the big practical advantage ?

I can do that in Oracle as well, as a practical example, in a theoretical relational model as well since columns can have any types, including relation types.

Or any example at all that is significant.

Best,
Costin Received on Wed Oct 22 2003 - 17:11:21 CEST