Re: Oracle and PICK

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.

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.

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)

My (limited) understanding of Oracle (excluding i series lines for the moment) is that there would be a requirement for a "header" table, and a "lines" table (with a foreign key to the header file) as a minimum.

You COULD implement this same "flat/2 dimensional" structure with the Pick Data model if you wanted to, just as, say, Oracle 10i (I believe) allows you to create a nested table structure that more closely mimics the "Pick" multi-dimensional/nested table structure.

(I don't want to get involved in a holy war as to which way is "right", as the answer will obviously vary with the platform, and also the problem at hand – there is rarely a black & white answer to any non-trivial task)

The main difference is that whilst this "nested table" approach is (relatively) new to the Oracle (SQL) fraternity, it has been one of the hallmarks of the multi-valued databases since inception (along with the fact that all fields can be arbitrarily "large" in a Pick structure – a memo field if you will – and also "small", which is reflected in the size of a record on disk, and also in RAM)

As Dawn has mentioned, the propagation of "relatively" unstructured information in the form of XML sits "easily" within the Pick data model, and I would therefore assume that the "I" series logic will feature in Oracles native support of XML.

(Note: that Raining Data, the successor to the original "Pick Systems" that commercialized the work of Dan Nielson & Dick Pick and brought the pick system to market, already have a native XML data store built on the Pick Data Model. The product is called "TigerLogic" – see www.rainingdata.com for details)

Anyway, I'm just trying to put oil on the water so that I can get a better/more accurate understanding of both sides of the argument

(As a sideline, how many here are old enough to recall the problems that the "relational" database had in displacing rigid Cobol/CODASYL thinking in the 70's – change is never easy)

andrewst_at_onetel.net.uk (Tony) wrote in message news:<c0e3f26e.0404161320.1f5617a7_at_posting.google.com>...
> "Nick" <nquinnusa_at_earthlink.net> wrote in message news:<fIUfc.10834$zj3.574_at_newsread3.news.atl.earthlink.net>...
> > Many, too many, years ago, I added a paragraph to my stand up sales routine
> > just for the 'xpurt' IT weenies,
> >
> > "How can you describe a three dimensional 'any' thing in a two dimensional
> > database?"
>
> What, pray tell, is a "two dimensional database"? Look at this simple
> table definition:
>
> create table three_dimensional_point( x int, y int, z int );
>
> How many dimensions does that table have?
Received on Sat Apr 17 2004 - 16:56:40 CEST