Home Page for the TeradataForum
 

Archives of the TeradataForum

Message Posted: Tue, 13 May 2003 @ 12:42:13 GMT


     
  <Prev Next>   <<First <Prev
Next>
Last>>
 


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



     
  <Prev Next>   <<First <Prev
Next>
Last>>
 
 
 
 
 
 
 
 
 
  
  Top Home Privacy Feedback  
 
 
Copyright for the TeradataForum (TDATA-L), Manta BlueSky    
Copyright 2016 - All Rights Reserved    
Last Modified: 27 Dec 2016