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/tropashko4.html
www.yafla.com/papers/sqlhierarchies/sqlhierarchies.htm
Articles by Joe Celko can be found at
www.searchdatabase.techtarget.com , e.g.
www.searchdatabase.techtarget.com/tip/1,289483,sid13_gci805976,00.html?FromTaxonomy=%2Fpr%2F282457 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
| 
