Re: Oracle and PICK

andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0404170953.cf441f0_at_posting.google.com>...
> ross_at_stamina.com.au (Ross Ferris) wrote in message news:<26f6cd63.0404170656.72605341_at_posting.google.com>...
> > Not being from an SQL planet, to my (very) untrained eye I'd say that
> > this was still a 2 dimensional table, with 1 row per record and 3
> > columns for integer data points, though I'm happy to concede that my
> > English reading may be incorrect.
>
> You are confusing the table with the 2-dimensional printout of the
> table's contents. You may as well say that both SQL tables and Pick
> table/files are 1-dimensional, because physically the data is all
> strung out as a linear sequence of bits on the disk.

I thought I was referring to how the DBMS treated the data - did this table simply have 3 columns proporting to represent a 3 dimension co-ordinate, or not ? Lacking a common technical background I was hoping to "grok" what you had proposed, which did NOT appear to correspond to a 3 dimensional data structure to me.

Also, I'd suggest that in both cases the data is PHYSICALLY stored 3 dimensionally on the disk .... but please, let not go there :-)

>
> > The term I've normally seen attributed to "Pick" (or multi-valued)
> > files/tables, is that they can implement a nested table, with repeated
> > column values "nested" within the structure.
>
> True. And many SQL databases (such as Oracle) now allow the same
> thing - unfortunately! In my experience, this new nested table
> capability is taken up enthusiastically by novices who (a) know no
> better, and (b) are positively encouraged to do so by books and
> training courses; people who really understand what they are doing
> (e.g. Oracle's guru Tom Kyte and, ahem, me ;-)) have nothing to do
> with them. This isn't Ludditism, it is wisdom.

Wisdom ? It is a wise man who knows he is wise !

I'm not aware of the rationale for the development of the "i" series, but I can only assume it is meant to bolster some deficiency, real or imagined, within the product itself.

Could it be that the "people who really understand" are aware of problems in that product when it comes to the implementation ? or shotcomings in the tools & query capabilities ?

Or is there a mathematical foundation to back up this belief ? The only serious work that I'm aware of that has been done on n-dimensional grammars was done by Ming Yue in the 1980's ….. but it's a subject I haven't researched for some time, and no doubt there have been advances in this area.

>
> > A real world example would be an invoice, comprising a header (who we
> > sold to, invoice # etc) and multiple lines (the actual products sold,
> > values etc).
> >
> > Just like a "real" invoice form, the "Pick" model would allow you to
> > create/model this structure in a single file, which in theory should
> > result in better performance, as a single I/O operation would return
> > both the header & line details (the lines may have further nested
> > tables [values] to record details like actual serial or batch numbers
> > sent, warehouse locations picked from etc, but I'll simplify it)
>
> This is logical/physical confusion. The fact that invoice headers and
> lines logically reside in 2 separate tables does not mean that they
> may not physically reside together on disk for performance reasons.
> Oracle, for example, allows 2 or more tables to be physically
> "clustered" together by key values.

OK, given that this thread includes "Oracle" in the topic, it is reasonable to look at the physical implementation capabilities of that product – just as I concluded that a single database I/O operation would be more efficient than multiple I/O's. (I'm assuming that with Oracle "clustering" the tables would still need to be "logically" joined to retrieve all of the Lines related to the header ?)

In the context of the overall TCO question, it might be "enlightening" to see if we could actually do a real world benchmark of the speed/performance of "clustered" data sets in Oracle with a "multi-valued" data structure with Pick …. Surely we have the technology available to look at real facts & figures ? Received on Sun Apr 18 2004 - 14:01:56 CEST