RE: Database hierarchies

  • From: "Chazhoor, Vincent" <Vincent.Chazhoor@xxxxxxxx>
  • To: "'Meenakshi.Aggarwal@xxxxxxxxxxxxx'" <Meenakshi.Aggarwal@xxxxxxxxxxxxx>, oracle-l@xxxxxxxxxxxxx
  • Date: Wed, 12 Jan 2005 10:04:53 -0500

There are two other approaches 
1.  Kimballs helper table apprach.  Please see the details at
     This will work even if you have multiple hierarchies.  If one employee
has one manager the Oracle parent_id apprach will work.  But if an employee
is reporting to one manager for administrative purposes and another managers
(project manager) for work related tasks then the apprach of using CONNECT
BY MAY not work.  But kimball's helper table apprach will work.  This is
mainly used in reporting/data warehouse environments.  But can be used for
OLTP also. 
The sample code forOracle databaseis available at kimball's website.
2. Joe celko's left number right number appraoch.
    This was introduced by Joe in his book 'SQL for smarties'.  You can get
the details at
This is a very flexible apprach and it will give good performance.  The only
issue is generating the left and right numbers are very complex.  There are
some sample code on the web for Sql serve.  May be you need to modify it for
oracle if this is the apprach that you want to go.



-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx]On Behalf Of Aggarwal, Meenakshi
Sent: Wednesday, January 12, 2005 8:46 AM
To: oracle-l@xxxxxxxxxxxxx
Subject: Database hierarchies


I have parent-child-child-child-.... (multiple hierarchies). What is the
best approach to create tables in data warehouse, when user have no clarity
on levels of hierarchies and I need to consider future increase in levels of
hierarchies, so I need to plan a structure that can absorb future

I saw one example on Does anybody have better way...

select rpad('*',2*level,'*')||ename EmpName, dname,
  2             sys_connect_by_path( ename, '/' ) cbp
  3    from emp
  4   start with mgr is null
  5   connect by prior empno = mgr
  6   order SIBLINGS by ename
  7  /

EMPNAME                        DNAME          CBP
------------------------------ -------------- ------------------------------
**KING                         ACCOUNTING     /KING
****BLAKE                      SALES          /KING/BLAKE
******ALLEN                    SALES          /KING/BLAKE/ALLEN
******JAMES                    SALES          /KING/BLAKE/JAMES
******MARTIN                   SALES          /KING/BLAKE/MARTIN
******TURNER                   SALES          /KING/BLAKE/TURNER
******WARD                     SALES          /KING/BLAKE/WARD
****CLARK                      ACCOUNTING     /KING/CLARK
******MILLER                   ACCOUNTING     /KING/CLARK/MILLER
****JONES                      RESEARCH       /KING/JONES
******FORD                     RESEARCH       /KING/JONES/FORD
********SMITH                  RESEARCH       /KING/JONES/FORD/SMITH
******SCOTT                    RESEARCH       /KING/JONES/SCOTT
********ADAMS                  RESEARCH       /KING/JONES/SCOTT/ADAMS


Other related posts: