Suppose you have a large number of flat hierarchies. As an example we take the emp table from the Scott schema.
Using it is we create a table Tree_emp as shown here:
create table tree_emp as select * from emp;
alter table tree_emp modify (empno number(18));
alter table tree_emp modify (mgr number(18));
INSERT INTO tree_emp
( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO
SELECT EMPNO+(10000*step.n) empno,
TRUNC(DBMS_RANDOM.value(low => 8, high => 50))*100 SAL,
(SELECT rownum n FROM dual CONNECT BY rownum <= 1000000
Show for all presidents (mgr is zero) the sum of the salaries of all subordinate . Write a pipelined table function that can navigate multiple trees simultaneously.
Use Oracle’s connect by Syntax.You can index as you like.