Archives of the TeradataForum
Message Posted: Tue, 13 May 2003 @ 12:42:13 GMT
| Subj: | | Re: Recursive sql |
| |
| From: | | Dieter N�th |
David Wellman wrote:
| | I think you'll find that the second option that John talks about below is the one used in Teradata to store the database and user
heirarchy. This information is accessed through view dbc.children. | |
| | In the underlying tables , if a user/database is at level 5 in the heirarchy (with DBC being at level 0), then this user/database
will appear in the Child column 5 times. The following query will return 5 rows. | |
| | sel * from dbc.children where child='xxx'; | |
No, this is no common way to store trees ;-)
There are three methods to store trees in SQL:
- "Adjacency list":
child and parent in one row, similar to the hierarchy of users in dbc.databases. Needs recursive SQL or cursors or lots of self-joins.
- "Nested set":
stores node and left/right information in a row, a child node is always between it's parent's left/right. No recursive SQL needed, but
overhead to maintain left/right for Insert/Update/Delete.
- "Path enumeration"
storing the path info for all parents in a row, e.g. 'dbc.sysdba.mydb'. Simple SQL, but again maintenance overhead.
Some links:
www.dbazine.com...
www.yafla.com...
Articles by Joe Celko can be found at
www.searchdatabase.techtarget.com,
e.g.
www.searchdatabase.techtarget.com...
and www.intelligententerprise.com
And there'll a new book by Celko this year about trees/hierarchies.
For Path enumeration there are some articles by Itzik Ben-Gan at www.sqlmag.com
Or just google for the keywords...
Dieter
| 
