Re: Oracle and PICK

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.

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

> 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. Received on Sat Apr 17 2004 - 19:53:37 CEST