Re: Hierarchical queries and indexes

From: --CELKO-- <jcelko212_at_earthlink.net>
Date: 5 Oct 2004 20:34:16 -0700
Message-ID: <18c7b3c2.0410051934.8576e0b_at_posting.google.com>


>> My thought is that it should be possible to create a table like

CREATE TABLE Ancestors
(fullname VARCHAR(100) NOT NULL PRIMARY KEY,  ancestor_fullname VARCHAR(100));
 CREATE INDEX anc_hier

     ON ancestor(ancestor_fullname CONNECT BY fullname); <<

But indexing is not part of SQL; look at the Standards. We thought it was too physical and stuck with constraints. The trouble with a CONNECT BY syntax is that it is a procedural, ordered traversal of the tree -- not at all set-oriented.

>>Celko - if you are reading this: <<

You know I Google for my name to feed my ego :)

>> You normally argue against explicit tuple numbering (auto-generated
surrogate keys), and I agree on the principle. <<

I am now using the better term "exposed physical locator" for auto-numbering, rowids, disk addresses, etc. -- anything that comes from the hardware and not the reality of the data.

>> However, aren't the lft/rgt attributes in the nested set model
actually instances of (specialized) tuple numbering? <<

No, they are "co-ordinates in a tree structure" and they carry a lot of information. For example, if I see ('a', 1, 48) I know 'a' is the root node of a tree with 24 nodes in it; if I see ('b', 2, 3) I know 'b' is a leaf node and the leftmost child under the root. If I see ('c', 13, 17) I know that I have a data error (the rule (lft-rgt+1)/2 = subtree size becomes (17-13+1)/2 give me 2.5 instead of a whole number). If I see the node ('z', 34, 35) I know I have an error because the root node told me thre were only 24 nodes in the tree.

The nested sets model models a tree as subtrees, not as a procedural traversal.

>> But since this is a theory-group, I hope someone is interested in
discussing why hierarchical encoding models have to exist. <<

Well, you have me :) Received on Wed Oct 06 2004 - 05:34:16 CEST

Original text of this message