Jun 10 2010
Related-Combine Operation „UNION ALL (RECURSIVE WITH)“
To make easier the interpretation of execution plans, in chapter 6 of TOP I defined three types of operations: standalone operations, unrelated-combine operations, and related-combine operations. For combine operations I also added a list of all operations of each type. Since in 11.2 a new related-combine operation is available, I decided to write this short post as addenda to the content of the book.
The new related-combine operation, named “UNION ALL (RECURSIVE WITH)”, is available to support the new recursive subquery factoring clause. Hence, it is used for hierarchical queries. The following query and its execution plan show an example:
SQL> WITH
2 e (xlevel, empno, ename, job, mgr, hiredate, sal, comm, deptno)
3 AS (
4 SELECT 1, empno, ename, job, mgr, hiredate, sal, comm, deptno
5 FROM emp
6 WHERE mgr IS NULL
7 UNION ALL
8 SELECT mgr.xlevel+1, emp.empno, emp.ename, emp.job, emp.mgr, emp.hiredate, emp.sal, emp.comm, emp.deptno
9 FROM emp, e mgr
10 WHERE emp.mgr = mgr.empno
11 )
12 SELECT *
13 FROM e;
-------------------------------------------------------------------------------
| Id | Operation | Name | Starts | A-Rows |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 14 |
| 1 | VIEW | | 1 | 14 |
| 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | 1 | 14 |
|* 3 | TABLE ACCESS FULL | EMP | 1 | 1 |
| 4 | NESTED LOOPS | | 4 | 13 |
| 5 | NESTED LOOPS | | 4 | 13 |
| 6 | RECURSIVE WITH PUMP | | 4 | 14 |
|* 7 | INDEX RANGE SCAN | EMP_MGR | 14 | 13 |
| 8 | TABLE ACCESS BY INDEX ROWID | EMP | 13 | 13 |
-------------------------------------------------------------------------------
3 - filter("MGR" IS NULL)
7 - access("EMP"."MGR"="MGR"."EMPNO")
filter("EMP"."MGR" IS NOT NULL)
Notice that there are actually two operations:
- UNION ALL (RECURSIVE WITH) BREADTH FIRST
- UNION ALL (RECURSIVE WITH) DEPTH FIRST
As their name suggest, the difference is due to the search clause that you can set to either BREADTH FIRST BY or DEPTH FIRST BY.
Reading an execution plan containing the “UNION ALL (RECURSIVE WITH)” operation is the same as reading one containing the “CONNECT BY WITH FILTERING” operation. As a matter of fact, the purpose of both operations is basically the same. Just notice that also the “PUMP” operation used in the execution plan differs. While in the former it is called “RECURSIVE WITH PUMP”, in the latter it is called “CONNECT BY PUMP”. But the difference, for the purpose of reading the execution plan, does not matter.
You find a full description on how to read such an execution plan in this post.
June 10th, 2010 at 9:33 am
Can you explain the differences in the statistics of the following two equivalent statements ?
Especially: the high number of “db block gets” (and “sorts”) in the “recursive with” – version ?
Thank you
SQL> set autotr traceonly arraysize 5000 pages 0 lines 200 SQL> variable n number SQL> exec :n:=1e6 PL/SQL procedure successfully completed. SQL> select level n from dual connect by level <= :n; 1000000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1236776825 ----------------------------------------------------------------------------- | Id | Operation | Name | Rows | Cost (%CPU)| Time | ----------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 2 (0)| 00:00:52 | |* 1 | CONNECT BY WITHOUT FILTERING| | | | | | 2 | FAST DUAL | | 1 | 2 (0)| 00:00:52 | ----------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 1 - filter(LEVEL<=TO_NUMBER(:N)) Statistics ---------------------------------------------------------- 0 recursive calls 0 db block gets 0 consistent gets 0 physical reads 0 redo size 6037377 bytes sent via SQL*Net to client 2713 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 1 sorts (memory) 0 sorts (disk) 1000000 rows processed SQL> with n(n) as (select 1 from dual union all select n+1 from n where n<:n) select * from n; 1000000 rows selected. Execution Plan ---------------------------------------------------------- Plan hash value: 1492144221 -------------------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 2 | 26 | 4 (0)| 00:01:44 | | 1 | VIEW | | 2 | 26 | 4 (0)| 00:01:44 | | 2 | UNION ALL (RECURSIVE WITH) BREADTH FIRST| | | | | | | 3 | FAST DUAL | | 1 | | 2 (0)| 00:00:52 | |* 4 | RECURSIVE WITH PUMP | | | | | | -------------------------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 4 - filter("N"<TO_NUMBER(:N)) Statistics ---------------------------------------------------------- 20 recursive calls 3350807 db block gets 0 consistent gets 0 physical reads 0 redo size 6037377 bytes sent via SQL*Net to client 2713 bytes received via SQL*Net from client 201 SQL*Net roundtrips to/from client 1000001 sorts (memory) 0 sorts (disk) 1000000 rows processedJune 10th, 2010 at 10:46 am
Hi Christian,
I did compare CONNECT BY and RECURSIVE WITH im my blog : http://laurentschneider.com/wordpress/2010/05/connect-by-and-recursive-with-part-2.html
Even with a similar plan, the performance massively differs …
Cheers
Laurent
June 10th, 2010 at 12:50 pm
Salut Laurent
I’m aware of the performance issues. Anyway, thank you for pointing them out. That might be interesting for some of my readers. I didn’t mention them because in this post I’m focusing on reading the executioin plan only.
Since we are already discussing problems related to the recursive subquery factoring clause, I would like to point out that also the stability is not the best one. E.g. this morning, while running a couple of queries to write the post, I got this nice error:
ORA-07445: exception encountered: core dump [qctcopn()+220] [SIGSEGV] [ADDR:0x14] [PC:0xF3A336C] [Address not mapped to object] []
Cheers,
Chris
June 13th, 2010 at 10:08 am
Hi Sokrates
> Can you explain the differences in the statistics of the following two equivalent statements ?
> Especially: the high number of “db block gets” (and “sorts”) in the “recursive with” – version ?
The high number of LIO is probably due to the sorts. You do not have enough PGA to do them in memory. If you increase the amount of available PGA you should be able to see an execution without LIO. In fact, because of the FAST DUAL operation, no LIO are necessary to “access” the data.
The number of sorts of such an execution plan is equal to the number of levels.
HTH
Chris