Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Database hierarchies
There are two other approaches
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_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Aggarwal, Meenakshi
Sent: Wednesday, January 12, 2005 8:46 AM
To: oracle-l_at_freelists.org
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
-- http://www.freelists.org/webpage/oracle-l -- http://www.freelists.org/webpage/oracle-lReceived on Wed Jan 12 2005 - 09:07:08 CST
![]() |
![]() |