Hierarchy Queries [message #678637] |
Mon, 23 December 2019 10:43 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
Hi Experts,
I am facing some difficulty to frame a sql query to achieve hierarchy. Please see the chronology of work i performed
CREATE TABLE abc.test_hier_tbl
(
employeeidnbr VARCHAR2 (15),
supervisoridnbr VARCHAR2 (15)
);
INSERT INTO ABC.TEST_HIER_TBL (EMPLOYEEIDNBR, SUPERVISORIDNBR)
VALUES ('000456', '000123');
INSERT INTO ABC.TEST_HIER_TBL (EMPLOYEEIDNBR, SUPERVISORIDNBR)
VALUES ('000789', '000123');
INSERT INTO ABC.TEST_HIER_TBL (EMPLOYEEIDNBR, SUPERVISORIDNBR)
VALUES ('0001234', '000456');
COMMIT;
Now my requirement is to list Total Reportee (direct or Indirect) for a Supervisor.
i.e.
EMPLOYEEIDNBR TOTALREPORTEE
------------- -------------
000123 3
000456 1
000789 0
SELECT count(1) TOTALREPORTEE
FROM abc.test_hier_tbl
CONNECT BY PRIOR employeeidnbr = Supervisoridnbr
START WITH Supervisoridnbr = '000123'
with above query i am getting the count but unable to display the employeeidnbr
Any pointer will be great help
Regards
Jay
|
|
|
|
|
|
Re: Hierarchy Queries [message #678644 is a reply to message #678642] |
Mon, 23 December 2019 15:17 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
Michel,
The Oracle version is
Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit Production
with cyclic value my table looks similar to
SELECT * FROM GHRA_STAGING_LAYER.TEST_HIER_TBL
EMPLOYEEIDNBR SUPERVISORIDNBR
------------- ---------------
000456 000123
000789 000123
0001234 000456
000123 000123
WITH employees
AS (SELECT DISTINCT EMPLOYEEIDNBR
FROM (SELECT EMPLOYEEIDNBR FROM GHRA_STAGING_LAYER.TEST_HIER_TBL
UNION ALL
SELECT SUPERVISORIDNBR FROM GHRA_STAGING_LAYER.TEST_HIER_TBL))
SELECT e.EMPLOYEEIDNBR,
( SELECT COUNT (*)
FROM GHRA_STAGING_LAYER.TEST_HIER_TBL t
CONNECT BY NOCYCLE PRIOR t.EMPLOYEEIDNBR = t.SUPERVISORIDNBR
START WITH t.SUPERVISORIDNBR = e.EMPLOYEEIDNBR)
TOTALREPORTEE
FROM employees e
ORDER BY 1
EMPLOYEEIDNBR TOTALREPORTEE
------------- -------------
000123 7
0001234 0
000456 1
000789 0
I am seeing the reportee count for 000123 is changed to 7.
let me know if i wrongly framed the query with NOCYCLE options
Also Please Not my Main table has more than 2 Million records, So I am not sure about the performance of query
[Updated on: Mon, 23 December 2019 15:46] Report message to a moderator
|
|
|
Re: Hierarchy Queries [message #678645 is a reply to message #678644] |
Mon, 23 December 2019 17:50 |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
SQL> select *
2 from test_hier_tbl
3 /
EMPLOYEEIDNBR SUPERVISORIDNBR
--------------- ---------------
000456 000123
000789 000123
0001234 000456
000123 000123
with t as (
select employeeidnbr,
sys_connect_by_path(employeeidnbr,'/') || '/' path
from test_hier_tbl
start with supervisoridnbr = employeeidnbr
connect by nocycle prior employeeidnbr = supervisoridnbr
)
select employeeidnbr,
totalreportee
from t
model
dimension by(path)
measures(employeeidnbr,0 totalreportee)
rules(
totalreportee[any] = count(*)[path like cv() || '%'] - 1
)
/
EMPLOYEEIDNBR TOTALREPORTEE
--------------- -------------
000123 3
000456 1
0001234 0
000789 0
SQL>
SY.
|
|
|
|
Re: Hierarchy Queries [message #678647 is a reply to message #678644] |
Tue, 24 December 2019 01:29 |
|
Michel Cadot
Messages: 68675 Registered: March 2007 Location: Nanterre, France, http://...
|
Senior Member Account Moderator |
|
|
If the only case you have a cycle is when an employee directly reports to him/herself then there is no need of NOCYCLE option and you can just modify the query as this:
SQL> select * from TEST_HIER_TBL order by 1, 2;
EMPLOYEEIDNBR SUPERVISORIDNBR
--------------- ---------------
000123 000123
0001234 000456
000456 000123
000789 000123
4 rows selected.
SQL> with
2 employees as (
3 select distinct EMPLOYEEIDNBR
4 from ( select EMPLOYEEIDNBR from TEST_HIER_TBL
5 union all
6 select SUPERVISORIDNBR from TEST_HIER_TBL
7 )
8 )
9 select e.EMPLOYEEIDNBR,
10 ( select count(*) from TEST_HIER_TBL t
11 connect by prior t.EMPLOYEEIDNBR = t.SUPERVISORIDNBR
12 start with t.SUPERVISORIDNBR = e.EMPLOYEEIDNBR
13 and t.SUPERVISORIDNBR != t.EMPLOYEEIDNBR )
14 TOTALREPORTEE
15 from employees e
16 order by 1
17 /
EMPLOYEEIDNBR TOTALREPORTEE
--------------- -------------
000123 3
0001234 0
000456 1
000789 0
4 rows selected.
[Updated on: Tue, 24 December 2019 02:10] Report message to a moderator
|
|
|
Re: Hierarchy Queries [message #678648 is a reply to message #678646] |
Tue, 24 December 2019 04:40 |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel Cadot wrote on Tue, 24 December 2019 01:37
Solomon,
With the original test case your query does not work:
Correct. My reply was to OP's post "with cyclic value my table looks similar to". In order to apply my solution to original post we need to add cyclic rows for supervisors who don't report to anyone (self-referencing rows):
SQL> select *
2 from test_hier_tbl
3 /
EMPLOYEEIDNBR SUPERVISORIDNBR
--------------- ---------------
000456 000123
000789 000123
0001234 000456
with t1 as (
select *
from test_hier_tbl
union
select supervisoridnbr,
supervisoridnbr
from test_hier_tbl
where supervisoridnbr not in (
select employeeidnbr
from test_hier_tbl
)
),
t2 as (
select employeeidnbr,
sys_connect_by_path(employeeidnbr,'/') || '/' path
from t1
start with supervisoridnbr = employeeidnbr
connect by nocycle prior employeeidnbr = supervisoridnbr
)
select employeeidnbr,
totalreportee
from t2
model
dimension by(path)
measures(employeeidnbr,0 totalreportee)
rules(
totalreportee[any] = count(*)[path like cv() || '%'] - 1
)
/
EMPLOYEEIDNBR TOTALREPORTEE
--------------- -------------
000123 3
000456 1
0001234 0
000789 0
SQL>
SY.
|
|
|
Re: Hierarchy Queries [message #678649 is a reply to message #678647] |
Tue, 24 December 2019 04:56 |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Michel,
Solution you posted is fine for small(er) hierarchy depth/table. For larger/deeper tables we lose performance since we are re-calculate fragments of same hierarchy many times. Model solution calculates hierarchy once only. Even this shallow hierarchy repeated 100,000 times mimicking 400,000 row table:
SQL> set timing on
SQL> begin
2 for v_i in 1..100000 loop
3 for v_rec in (
4 with t as (
5 select employeeidnbr,
6 sys_connect_by_path(employeeidnbr,'/') || '/' path
7 from test_hier_tbl
8 start with supervisoridnbr = employeeidnbr
9 connect by nocycle prior employeeidnbr = supervisoridnbr
10 )
11 select employeeidnbr,
12 totalreportee
13 from t
14 model
15 dimension by(path)
16 measures(employeeidnbr,0 totalreportee)
17 rules(
18 totalreportee[any] = count(*)[path like cv() || '%'] - 1
19 )
20 ) loop
21 null;
22 end loop;
23 end loop;
24 end;
25 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:16.62
SQL> begin
2 for v_i in 1..100000 loop
3 for v_rec in (
4 with
5 employees as (
6 select distinct EMPLOYEEIDNBR
7 from ( select EMPLOYEEIDNBR from TEST_HIER_TBL
8 union all
9 select SUPERVISORIDNBR from TEST_HIER_TBL
10 )
11 )
12 select e.EMPLOYEEIDNBR,
13 ( select count(*) from TEST_HIER_TBL t
14 connect by prior t.EMPLOYEEIDNBR = t.SUPERVISORIDNBR
15 start with t.SUPERVISORIDNBR = e.EMPLOYEEIDNBR
16 and t.SUPERVISORIDNBR != t.EMPLOYEEIDNBR )
17 TOTALREPORTEE
18 from employees e
19 ) loop
20 null;
21 end loop;
22 end loop;
23 end;
24 /
PL/SQL procedure successfully completed.
Elapsed: 00:00:17.99
SQL>
As you can see model is about 10% faster. With deeper hierarchies calculating hierarchy once only solution will provide even better performance improvement comparing to re-calculating same hierarchy fragments.
SY.
[Updated on: Tue, 24 December 2019 05:01] Report message to a moderator
|
|
|
|
|
|
|
|
Re: Hierarchy Queries [message #678655 is a reply to message #678654] |
Tue, 24 December 2019 14:40 |
Solomon Yakobson
Messages: 3280 Registered: January 2010 Location: Connecticut, USA
|
Senior Member |
|
|
Also, index can improve performance:
SQL> set linesize 132
SQL> explain plan for
2 select employeeidnbr,
3 sys_connect_by_path(employeeidnbr,'/') || '/' path
4 from test_hier_tbl
5 start with supervisoridnbr = employeeidnbr
6 connect by nocycle prior employeeidnbr = supervisoridnbr
7 /
Explained.
SQL> select *
2 from table(dbms_xplan.display)
3 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 1878197020
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 54 | 4 (25)| 00:00:01 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | TEST_HIER_TBL | 4 | 56 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
---------------------------------------------------
1 - access("SUPERVISORIDNBR"=PRIOR "EMPLOYEEIDNBR")
filter("SUPERVISORIDNBR"="EMPLOYEEIDNBR")
15 rows selected.
SQL> create index test_hier_tbl_idx1 on test_hier_tbl(supervisoridnbr,employeeidnbr)
2 /
Index created.
SQL> explain plan for
2 select employeeidnbr,
3 sys_connect_by_path(employeeidnbr,'/') || '/' path
4 from test_hier_tbl
5 start with supervisoridnbr = employeeidnbr
6 connect by nocycle prior employeeidnbr = supervisoridnbr
7 /
Explained.
SQL> select *
2 from table(dbms_xplan.display)
3 /
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
Plan hash value: 1109593249
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 3 | 54 | 5 (40)| 00:00:01 |
|* 1 | CONNECT BY WITH FILTERING| | | | | |
|* 2 | INDEX FULL SCAN | TEST_HIER_TBL_IDX1 | 1 | 14 | 1 (0)| 00:00:01 |
| 3 | NESTED LOOPS | | 2 | 46 | 2 (0)| 00:00:01 |
| 4 | CONNECT BY PUMP | | | | | |
|* 5 | INDEX RANGE SCAN | TEST_HIER_TBL_IDX1 | 2 | 28 | 1 (0)| 00:00:01 |
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SUPERVISORIDNBR"=PRIOR "EMPLOYEEIDNBR")
2 - filter("SUPERVISORIDNBR"="EMPLOYEEIDNBR")
5 - access("connect$_by$_pump$_002"."prior employeeidnbr "="SUPERVISORIDNBR")
Note
-----
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------
- this is an adaptive plan
23 rows selected.
SQL>
SY.
|
|
|
Re: Hierarchy Queries [message #678779 is a reply to message #678655] |
Fri, 10 January 2020 10:22 |
|
msinha8
Messages: 43 Registered: March 2014 Location: Hyderbad
|
Member |
|
|
HI,
Happy New Year!!
Explain Plan :
EXPLAIN PLAN
FOR
SELECT employeeidnbr,
SYS_CONNECT_BY_PATH (employeeidnbr, '/') || '/' PATH
FROM test_hier_tbl
START WITH supervisoridnbr = employeeidnbr
CONNECT BY NOCYCLE PRIOR employeeidnbr = supervisoridnbr
SELECT * FROM TABLE (DBMS_XPLAN.display)
Plan hash value: 2916366004
---------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1541 | 21574 | 10378 (49)| 00:02:05 |
|* 1 | CONNECT BY NO FILTERING WITH START-WITH| | | | | |
| 2 | TABLE ACCESS FULL | TEST_HIER_TBL | 990K| 13M| 5424 (1)| 00:01:06 |
---------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SUPERVISORIDNBR"=PRIOR "EMPLOYEEIDNBR")
filter("SUPERVISORIDNBR"="EMPLOYEEIDNBR")
Note
-----
- dynamic sampling used for this statement (level=2)
Explain Plan Post Index Creation
Plan hash value: 1134490305
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1541 | 21574 | 2575 (2)| 00:00:31 |
|* 1 | CONNECT BY WITH FILTERING| | | | | |
|* 2 | INDEX FAST FULL SCAN | TEST_HIER_TBL_IDX1 | 1 | 14 | 1282 (2)| 00:00:16 |
| 3 | NESTED LOOPS | | 1540 | 32340 | 1291 (2)| 00:00:16 |
| 4 | CONNECT BY PUMP | | | | | |
|* 5 | INDEX RANGE SCAN | TEST_HIER_TBL_IDX1 | 1540 | 21560 | 9 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("SUPERVISORIDNBR"=PRIOR "EMPLOYEEIDNBR")
2 - filter("SUPERVISORIDNBR"="EMPLOYEEIDNBR")
5 - access("connect$_by$_pump$_002"."PRIOR employeeidnbr "="SUPERVISORIDNBR")
Note
-----
- dynamic sampling used for this statement (level=2)
[Updated on: Fri, 10 January 2020 10:34] Report message to a moderator
|
|
|