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