Striving for Optimal Performance
  • Blog
    • Archive
    • Categories
    • Search
  • Troubleshooting Oracle Performance
    • Description
    • Structure
    • Table of Contents
    • Forewords
    • Reviews
    • Downloadable Files
    • Addenda and Errata
    • First Edition
  • Public Appearances
    • Past Public Appearances
  • Contact
  • About

Related-Combine Operation „UNION ALL (RECURSIVE WITH)“

10 June 2010 5 Comments Written by Christian Antognini

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.

11gR2, Query Optimizer, TOP
Evolution of a SQL Plan Baseline Based on a DELETE Statement
Troubleshooting Oracle Performance – Downloadable Files

5 Comments

1 Ping/Trackback

  1. Sokrates Sokrates
    10 June 2010    

    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"
    
    
    
    Reply
    • Christian Antognini Christian Antognini
      13 June 2010    

      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

      Reply
  2. Laurent Schneider Laurent Schneider
    10 June 2010    

    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

    Reply
    • Christian Antognini Christian Antognini
      10 June 2010    

      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

      Reply
  3. WITH-Clause Reloaded: Hierarchie und Rekursion | Oraculix WITH-Clause Reloaded: Hierarchie und Rekursion | Oraculix
    26 February 2014    

    […] Christian Antognini: Related-Combine Operation „UNION ALL (RECURSIVE WITH) […]

  1. WITH-Clause Reloaded: Hierarchie und Rekursion | Oraculix on 26 February 2014 at 15:36

Leave a Reply Cancel reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.