Home » SQL & PL/SQL » SQL & PL/SQL » Hierarchy Queries
Hierarchy Queries [message #678637] Mon, 23 December 2019 10:43 Go to next message
msinha8
Messages: 42
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 #678638 is a reply to message #678637] Mon, 23 December 2019 12:23 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Here's one way:
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           TOTALREPORTEE
 14  from employees e
 15  order by 1
 16  /
EMPLOYEEIDNBR   TOTALREPORTEE
--------------- -------------
000123                      3
0001234                     0
000456                      1
000789                      0

4 rows selected.
Re: Hierarchy Queries [message #678639 is a reply to message #678638] Mon, 23 December 2019 13:01 Go to previous messageGo to next message
msinha8
Messages: 42
Registered: March 2014
Location: Hyderbad
Member
Thanks Micheal for the inputs, now i am trying to see if i can eliminate the cyclic issue (i.e. a employee is supervisor of itself)

Thanks
Jay
Re: Hierarchy Queries [message #678642 is a reply to message #678639] Mon, 23 December 2019 14:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

What's your Oracle version?
In 11.2 and up you have the NOCYCLE option.

Re: Hierarchy Queries [message #678644 is a reply to message #678642] Mon, 23 December 2019 15:17 Go to previous messageGo to next message
msinha8
Messages: 42
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 Sad

[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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
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 #678646 is a reply to message #678645] Tue, 24 December 2019 00:37 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

Solomon,

With the original test case your query does not work:
SQL> with t as (
  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            )
  8  select  employeeidnbr,
  9          totalreportee
 10    from  t
 11    model
 12      dimension by(path)
 13      measures(employeeidnbr,0 totalreportee)
 14      rules(
 15            totalreportee[any] = count(*)[path like cv() || '%'] - 1
 16           )
 17  /

no rows selected
Re: Hierarchy Queries [message #678647 is a reply to message #678644] Tue, 24 December 2019 01:29 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
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 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
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 #678650 is a reply to message #678649] Tue, 24 December 2019 05:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I agree that MODEL is a far better option to get better performances with no doubts (I showed it in several topics when this new option was released).
I also suspect there are several faster ways in a single steps but, in this Christmas Eve, I have not so much time to investigate.

Re: Hierarchy Queries [message #678651 is a reply to message #678650] Tue, 24 December 2019 10:11 Go to previous messageGo to next message
msinha8
Messages: 42
Registered: March 2014
Location: Hyderbad
Member
Thank Michel,SY

Thanks for the all the help provided.

Just Quick Info, With MODEL Cluse, my query is returning data in 1+ hrs Sad

Regards
Jay
Re: Hierarchy Queries [message #678652 is a reply to message #678651] Tue, 24 December 2019 10:49 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

And the other query?

Re: Hierarchy Queries [message #678653 is a reply to message #678651] Tue, 24 December 2019 11:54 Go to previous messageGo to next message
Michel Cadot
Messages: 66794
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator

I think the following one would be faster:
SQL> with
  2    employees as (
  3      select EMPLOYEEIDNBR from TEST_HIER_TBL
  4      union
  5      select SUPERVISORIDNBR from TEST_HIER_TBL
  6    ),
  7    reportees as (
  8      select connect_by_root SUPERVISORIDNBR supervisor, EMPLOYEEIDNBR reportee
  9      from TEST_HIER_TBL t
 10      connect by prior t.EMPLOYEEIDNBR = t.SUPERVISORIDNBR
 11      start with     t.SUPERVISORIDNBR in (select EMPLOYEEIDNBR from employees)
 12                 and t.SUPERVISORIDNBR != t.EMPLOYEEIDNBR
 13    )
 14  select EMPLOYEEIDNBR, count(distinct reportee) TOTALREPORTEE
 15  from employees left outer join reportees on supervisor = EMPLOYEEIDNBR
 16  group by EMPLOYEEIDNBR
 17  order by EMPLOYEEIDNBR
 18  /
EMPLOYEEIDNBR   TOTALREPORTEE
--------------- -------------
000123                      3
0001234                     0
000456                      1
000789                      0
Re: Hierarchy Queries [message #678654 is a reply to message #678651] Tue, 24 December 2019 14:34 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
Registered: January 2010
Location: Connecticut, USA
Senior Member
msinha8 wrote on Tue, 24 December 2019 11:11
Thank Michel,SY

Just Quick Info, With MODEL Cluse, my query is returning data in 1+ hrs Sad
Post explain plan.

SY.

Re: Hierarchy Queries [message #678655 is a reply to message #678654] Tue, 24 December 2019 14:40 Go to previous messageGo to next message
Solomon Yakobson
Messages: 2908
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 Go to previous message
msinha8
Messages: 42
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

Previous Topic: DML operation on a table
Next Topic: ORA-00604 & ORA-28003 - Even when not trying to change password
Goto Forum:
  


Current Time: Thu Jan 23 23:22:18 CST 2020