RE: Database hierarchies

  • From: "Chazhoor, Vincent" <Vincent.Chazhoor@xxxxxxxx>
  • To: "'Aggarwal, Meenakshi'" <Meenakshi.Aggarwal@xxxxxxxxxxxxx>
  • Date: Thu, 13 Jan 2005 10:21:19 -0500

Sorry.  Please try
http://www.intelligententerprise.com/001020/celko1_1.jhtml

-----Original Message-----
From: Aggarwal, Meenakshi [mailto:Meenakshi.Aggarwal@xxxxxxxxxxxxx]
Sent: Thursday, January 13, 2005 9:01 AM
To: Chazhoor, Vincent
Cc: oracle-l@xxxxxxxxxxxxx
Subject: RE: Database hierarchies


Hi Vincent,

Thanks for providing me the links. I could access the first link but was not
able to open second link. I would appreciate if you can send second link
again.

Thanks
Meenakshi

-----Original Message-----
From: Chazhoor, Vincent [mailto:Vincent.Chazhoor@xxxxxxxx]
Sent: Wednesday, January 12, 2005 10:05 AM
To: Aggarwal, Meenakshi; oracle-l@xxxxxxxxxxxxx
Subject: RE: Database hierarchies


There are two other approaches 
 
1.  Kimballs helper table apprach.  Please see the details at
 
http://www.google.ca/search?q=cache:s8k19JsELzIJ:www.dbmsmag.com/9809d05.htm
l+kimball+helper+tables+hierarchies+fact+table&hl=en
 
     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 
 
http://www.google.ca/search?q=cache:tl_CTRYtCQYJ:www.intelligententerprise.c
om/001020/celko.shtml+Joe+left+number+right+number+hierarchies&hl=en
 
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.

Thanks,

Vincent

-----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


Hi,

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
hierarchies.

I saw one example on asktom.oracle.com. 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


Thanks
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l

Other related posts: