Principle of Oracle recursive query

In Oracle 10 Gb, he came to scott user to perform experiments on nodes on layers 1, 2, 3, and 4 respectively:

When start with is the root node (level = 1), to check its subnodes, connect by pump and emp are scanned four times (total level ).

When start with is the root node (level = 2), connect by pump and emp are scanned three times.

When start with is the root node (level = 3), connect by pump and emp are scanned twice.

When start with is the root node (level = 4), connect by pump and emp are scanned once to check its subnodes.

Note that leve = 2 and level = 3 are not leaf nodes. If they are leaf nodes, connect by pump and emp scan only once.

Operation Name Starts

FILTER
Table access full emp 1
HASH JOIN
Connect by pump 4
Table access full emp 4

Let me explain the above execution plan. Taking start with ename = 'King' as an example, it shows that the EMP uses the "ENAME" = 'King' filter to find the node as the root node (Set ), from set A to all nodes that meet the conditions at the next level (Set B), from Set B to all nodes that meet the conditions at the next level (set C ), connect by pump several times if the tree has several levels.

SQL> set pagesize 100
SQL> -- root node level = 1
SQL> select e. empno, e. ename, e. mgr, e. deptno, level
From emp e
Start with ename = 'King'
Connect by prior empno = mgr;

EMPNO ENAME MGR DEPTNO LEVEL
--------------------------------------------------
7839 KING 10 1
7566 JONES 7839 20 2
7788 SCOTT 7566 20 3
7876 ADAMS 7788 20 4
7902 FORD 7566 20 3
7369 SMITH 7902 20 4
7698 BLAKE 7839 30 2
7499 ALLEN 7698 30 3
7521 WARD 7698 30 3
7654 MARTIN 7698 30 3
7844 TURNER 7698 30 3
7900 JAMES 7698 30 3
7782 CLARK 7839 10 2
7934 MILLER 7782 10 3
14 rows have been selected.
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Bytes -----------------------------------------------------------------------------------------------------------------------
Bytes ----------------------------------------------------------------------------------------------------
SQL _ID 6as71p9t5arg3, child number 0
-------------------------------------
Select e. empno, e. ename, e. mgr, e. deptno, level from emp e start with ename = 'King' connect by prior empno
= Mgr
Plan hash value: 3364448299
Bytes -----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
Bytes -----------------------------------------------------------------------------------------------------------------------
| * 1 | connect by with filtering | 1 | 14 | 00:00:00. 01 | 35 | 9216 | 9216 | 8192 (0) |
| * 2 | FILTER | 1 | 1 | 00:00:00. 01 | 7 |
| 3 | table access full | EMP | 1 | 14 | 14 | 00:00:00. 01 | 7 |
| * 4 | hash join | 4 | 13 | 00:00:00. 01 | 28 | 1036K | 1036K | 776 K (0) |
| 5 | connect by pump | 4 | 14 | 00:00:00. 01 | 0 |
| 6 | table access full | EMP | 4 | 14 | 56 | 00:00:00. 01 | 28 |
| 7 | table access full | EMP | 0 | 14 | 0 | 00:00:00. 01 | 0 |
Bytes -----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("ENAME" = 'King ')
2-filter ("ENAME" = 'King ')
4-access ("MGR" = NULL)

SQL> -- level = 2
SQL> select e. empno, e. ename, e. mgr, e. deptno, level
From emp e
Start with ename = 'Jones'
Connect by prior empno = mgr;
EMPNO ENAME MGR DEPTNO LEVEL
--------------------------------------------------
7566 JONES 7839 20 1
7788 SCOTT 7566 20 2
7876 ADAMS 7788 20 3
7902 FORD 7566 20 2
7369 SMITH 7902 20 3
SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Bytes ------------------------------------------------------------------------------------------------------------------------
SQL _ID 2bc1_vmbyg7a5, child number 1
-------------------------------------
Select e. empno, e. ename, e. mgr, e. deptno, level from emp e start with ename = 'Jones 'connect by prior empno
= Mgr
Plan hash value: 3364448299
Bytes -----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
Bytes -----------------------------------------------------------------------------------------------------------------------
| * 1 | connect by with filtering | 1 | 5 | 00:00:00. 01 | 28 | 9216 | 9216 | 8192 (0) |
| * 2 | FILTER | 1 | 1 | 00:00:00. 01 | 7 |
| 3 | table access full | EMP | 1 | 14 | 14 | 00:00:00. 01 | 7 |
| * 4 | hash join | 3 | 4 | 00:00:00. 01 | 21 | 1036K | 1036K | 404 K (0) |
| 5 | connect by pump | 3 | 5 | 00:00:00. 01 | 0 |
| 6 | table access full | EMP | 3 | 14 | 42 | 00:00:00. 01 | 21 |
| 7 | table access full | EMP | 0 | 14 | 0 | 00:00:00. 01 | 0 |
Bytes -----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("ENAME" = 'Jones ')
2-filter ("ENAME" = 'Jones ')
4-access ("MGR" = NULL)

SQL> -- level = 3
SQL> select e. empno, e. ename, e. mgr, e. deptno, level
From emp e
Start with ename = 'Scott'
Connect by prior empno = mgr;
EMPNO ENAME MGR DEPTNO LEVEL
--------------------------------------------------
7788 SCOTT 7566 20 1
7876 ADAMS 7788 20 2

SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));

PLAN_TABLE_OUTPUT
Bytes -----------------------------------------------------------------------------------------------------------------------
SQL _ID fqf7r75c9atqv, child number 0
-------------------------------------
Select e. empno, e. ename, e. mgr, e. deptno, level from emp e start with ename = 'Scott 'connect by prior empno
= Mgr
Plan hash value: 3364448299
Bytes -----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
Bytes -----------------------------------------------------------------------------------------------------------------------
| * 1 | connect by with filtering | 1 | 2 | 00:00:00. 01 | 21 | 9216 | 9216 | 8192 (0) |
| * 2 | FILTER | 1 | 1 | 00:00:00. 01 | 7 |
| 3 | table access full | EMP | 1 | 14 | 14 | 00:00:00. 01 | 7 |
| * 4 | hash join | 2 | 1 | 00:00:00. 01 | 14 | 1036K | 1036K | 282 K (0) |
| 5 | connect by pump | 2 | 2 | 00:00:00. 01 | 0 |
| 6 | table access full | EMP | 2 | 14 | 28 | 00:00:00. 01 | 14 |
| 7 | table access full | EMP | 0 | 14 | 0 | 00:00:00. 01 | 0 |
Bytes -----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("ENAME" = 'Scott ')
2-filter ("ENAME" = 'Scott ')
4-access ("MGR" = NULL)

SQL> -- level = 4
SQL> select e. empno, e. ename, e. mgr, e. deptno, level
From emp e
Start with ename = 'Smith'
Connect by prior empno = mgr;
EMPNO ENAME MGR DEPTNO LEVEL
--------------------------------------------------
7369 SMITH 7902 20 1

SQL> select * from table (dbms_xplan.display_cursor (null, null, 'allstats last '));
PLAN_TABLE_OUTPUT
Bytes -----------------------------------------------------------------------------------------------------------------------
SQL _ID f5fvjuk1j8mak, child number 1
-------------------------------------
Select e. empno, e. ename, e. mgr, e. deptno, level from emp e start with ename = 'Smith 'connect by prior empno
= Mgr
Plan hash value: 3364448299
Bytes -----------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem |
Bytes -----------------------------------------------------------------------------------------------------------------------
| * 1 | connect by with filtering | 1 | 1 | 00:00:00. 01 | 14 | 9216 | 9216 | 8192 (0) |
| * 2 | FILTER | 1 | 1 | 00:00:00. 01 | 7 |
| 3 | table access full | EMP | 1 | 14 | 14 | 00:00:00. 01 | 7 |
| * 4 | hash join | 1 | 0 | 00:00:00. 01 | 7 | 1036K | 1036K | 318 K (0) |
| 5 | connect by pump | 1 | 1 | 00:00:00. 01 | 0 |
| 6 | table access full | EMP | 1 | 14 | 14 | 00:00:00. 01 | 7 |
| 7 | table access full | EMP | 0 | 14 | 0 | 00:00:00. 01 | 0 |
Bytes -----------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id ):
---------------------------------------------------
1-filter ("ENAME" = 'Smith ')
2-filter ("ENAME" = 'Smith ')
4-access ("MGR" = NULL)