Efficiently pulling parent-child relationships from the same table 1

[nagornyi]

The table TBL keeps manager – employee relations by keeping both MGR_ID and PER_ID. For different reasons there are multiple entries of the same employee for a given manager. So, to pick all the employees for the given manager I use the SQL

SELECT DISTINCT
T1.PER_ID 
FROM 
TBL T1
WHERE 1=1
AND T1.MGR_ID = :P_MGR_ID

Now, every selected employee can be a manager for the next level of employees, and I need to pick those also. Total there can be up to 8 levels of management, so to pick all the employees on all level for the given manager I use the SQL

SELECT DISTINCT
T1.PER_ID 
FROM 
TBL T1
WHERE 1=1
AND T1.MGR_ID = :P_MGR_ID

union all ------- level2 

SELECT DISTINCT
T2.PER_ID 
FROM 
  TBL T1
, TBL T2
WHERE 1=1
AND T1.MGR_ID = :P_MGR_ID
AND T2.MGR_ID = T1.EMP_ID

union all ------- level3

SELECT DISTINCT
T3.PER_ID 
FROM 
  TBL T1
, TBL T2
, TBL T3
WHERE 1=1
AND T1.MGR_ID = :P_MGR_ID
AND T2.MGR_ID = T1.EMP_ID
AND T3.MGR_ID = T2.EMP_ID

union all  ------- levels 4, 5, 6, 7 , 8  
………

The SQL runs fine, the only problem Is performance. There are about 250,000 records in the TBL. When the manager has about 1000 employees, the SQL runs about 1 min, which is acceptable. But for those having 4000 and more it takes about 1 hour. My question is how can I better build the query to increase the performance.
Thanks.

 

[LKBrwnDBA]

Use a hierarchical query:

SELECT RPAD(' ',LEVEL*3)||' '||T1.EMP_ID||' - '||T1.ENAME
  FROM TBL T1
 START WITH T1.MGR_ID = :P_MGR_ID
    CONNECT BY PRIOR T1.EMP_ID = T1.MGR_ID;

And create an index on (MGR_ID, EMP_ID).


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[nagornyi]

Thank you. This helped me to solve the problem. I had an issue with select distinct, though. If I add distinct right to your SQL, it takes forever to complete. So I replace TBL with a subquery that selects distinct manager-employee pairs, and that works with acceptable performance.