Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)

Here’s a little trick question. Check out the execution plan below.

What the hell, shouldn’t the INDEX/TABLE access be the other way around?!

Also, how come it’s TABLE ACCESS FULL (and not by INDEX ROWID) in there?

This question is with a little gotcha, but can you come up with a query which produced such plan? ;-)

----------------------------------------------
| Id  | Operation          | Name   | E-Rows |
----------------------------------------------
|   0 | SELECT STATEMENT   |        |        |
|*  1 |  INDEX RANGE SCAN  | PK_EMP |      1 |
|*  2 |   TABLE ACCESS FULL| EMP    |      1 |
----------------------------------------------
This entry was posted in Cool stuff, Oracle and tagged , . Bookmark the permalink.

14 Responses to Execution plan Quiz: Shouldn’t these row sources be the other way around ;-)

  1. Marcus Mönnig says:

    This is a getting a bit weird, but it’s nice geek fun… :-)

    I can produce that execution plan like below, but it will only succeed if there is only a single entry in scott.emp.

    SQL" explain plan for select e.empno from scott.emp e where e.empno"=(select empno from scott.emp where ename=’Tanel’);

    Explained.

    SQL" select * from table(dbms_xplan.display(‘plan_table’));

    PLAN_TABLE_OUTPUT
    ——————————————————————————–

    Plan hash value: 525803923

    —————————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
    —————————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 13 | 5 (0)| 00:00:01 |
    |* 1 | INDEX RANGE SCAN | PK_EMP | 1 | 13 | 2 (0)| 00:00:01 |
    |* 2 | TABLE ACCESS FULL| EMP | 1 | 20 | 3 (0)| 00:00:01 |
    —————————————————————————–

    Predicate Information (identified by operation id):

    PLAN_TABLE_OUTPUT
    ——————————————————————————–

    —————————————————

    1 – access(“E”.”EMPNO”<= (SELECT /*+ */ "EMPNO" FROM "SCOTT"."EMP"
    "EMP" WHERE "ENAME"='Tanel'))
    2 – filter("ENAME"='Tanel')

    Note
    —–
    – dynamic sampling used for this statement

    20 rows selected.

  2. Marcus Mönnig says:

    …quotes should be > (greater than) characters above…

    @Marcus Mönnig

  3. Ron Crisco says:

    select (select empno from emp where empno between -1 and 1) empno, deptno from emp;

  4. Sean says:

    explain plan for select (select e.emp_id from emp e where e.emp_id =1) from emp e2 where e2.emp_name=’abba';

    —————————————————————–
    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
    —————————————————————–
    | 0 | SELECT STATEMENT | | 1 | 8 | 3 (0)|
    | 1 | INDEX RANGE SCAN | PK_EMP | 1 | 3 | 1 (0)|
    | 2 | TABLE ACCESS FULL| EMP | 1 | 8 | 3 (0)|
    —————————————————————–

    Note: non-unique (deferrable) index named pk_emp on emp(emp_id)

  5. Tanel Poder says:

    @Marcus Mönnig
    Yeah, Marcus, correct! I think I need to start coming up with trickier trick questions ;-)

    The trick was to use a single-row non-correlated subquery (the subquery must return max 1 row, otherwise the query fill fail with “ORA-01427: single-row subquery returns more than one row”). So, there were 2 query blocks involved instead of one.

    The index range scan row source (of the parent query) first checks whether it would get any rows from the index (by fetching the first row) and if there is a row, then the index range scan row source “recursively” calls the table access row source of the non-correlated IN subquery. The single row coming from the subquery is going to be cached and the control is returned back to index range scan, which continues fetching all rows it can and compares these to the cached row from the subquery.

    This is one of the special cases where the first data access (logical IO) does not start from the first LEAF of the execution plan tree, but a bit higher.

    If the index range scan doesn’t get any rows from its index, then it never invokes the table scan under it, as there’s nothing to compare the subquery result to.

    I plan to write a bit longer post about this once I have time.

  6. Tanel Poder says:

    Sean and Ron: Your examples used scalar subqueries, which are executed by the parent function of that index range scan (the fetch function shown as SELECT STATEMENT in this case). The execution plan output is not exactly what I showed (note the difference in indentation, the parent of the TABLE ACCESS FULL is not the index range scan in your examples, but the SELECT STATEMENT :)

  7. Narendra says:

    Bingo!!
    I remember seeing similar plans on oracle forum posts some time back and I was almost sure (then) that either the OP has made mistake in copy-paste or it was due to some issues with the way DBMS_XPLAN.DISPLAY handles indentation. Now, I know I was wrong. Thanks a lot for this lesson learnt.

  8. @Narendra

    Narenda,
    There are cases where the indentation of multiple subqueries goes badly wrong because the “depth” column is calculated incorrectly – it’s possible that some of the oddities you’ve seen are due to this.

    Most of these seem to be fixed in 10.2.0.4 and 11.2.0.1, but if you want an example take a look at the execution plan from 10.2.0.3 for
    select * from all_objects;

    There is a filter operaiton which should have multiple ‘fixed table full x$kzspr’ child operations; instead each of these child rows indents one row to the left of the previous one … and there are lots of them.

  9. @Tanel Poder

    Tanel,
    In this example your analysis of Marcus’ plan is not correct.

    If you check the predicate section, you will see that the predicate is “column <= subquery" is an ACCESS predicate. This means the subquery (tablescan) has to run first to generate a value that is used to drive the index range scan. You can confirm this by cloning the EMP table and using the clone in the subquery, then flushing the buffer cache and using sql_trace to watch the order of physical I/O.

    Your description of one case when the execution ordering "rule" breaks, though, is correct. If the optimizer uses a filter against an index scan, then the plan comes out "the wrong shape". My assumption when I saw your example was that your 'gotcha' was that EMP wasn't scott.emp and that it had a two-column primary key:

    (Hoping the WordPress "sourcecode" tag works here)

    create table emp (
    	n1, n2,
    	constraint emp_pk primary key(n1,n2)
    )
    as
    select
    	rownum	n1,
    	rownum	n2
    from
    	all_objects
    where
    	rownum <= 1000
    ;
    
    -- collect stats here
    
    explain plan for
    select 
    	e1.n1, e1.n2
    from 
    	emp e1
    where 	
    	e1.n1 = :b1
    and	e1.n2	= (
    		select /*+ full(e2) */ 
    			e2.n1 
    		from 
    			emp e2 
    		where	e2.n2 = e1.n1
    	)
    ;
    
    select * from table(dbms_xplan.display);
    
    -------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost  |
    -------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |     1 |     7 |     4 |
    |*  1 |  INDEX RANGE SCAN  | EMP_PK |     1 |     7 |     2 |
    |*  2 |   TABLE ACCESS FULL| EMP    |     1 |     7 |     2 |
    -------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       1 - access("E1"."N1"=TO_NUMBER(:B1))
           filter("E1"."N2"= (SELECT /*+ FULL ("E2") */ "E2"."N1" FROM
                  "EMP" "E2" WHERE "E2"."N2"=:B1))
       2 - filter("E2"."N2"=:B1)
    
    
    
    

    If it's all come out correctly, you can see that my input bind drives a ranges scan with an ACCESS predicate, and then the subquery operates as a FILTER predicate. Oracle seem to have folded a "filter operation" into a "filter predicate" and butchered the plan as a consequence. The "correct" execution plan (and one that you might still see in some cases in 8.1.7.4 is:

    
    -------------------------------------------------------------
    | Id  | Operation          | Name   | Rows  | Bytes | Cost  |
    -------------------------------------------------------------
    |   0 | SELECT STATEMENT   |        |     1 |     7 |     4 |
    |* 1a |  FILTER            |        |       |       |       |
    |* 1b |   INDEX RANGE SCAN | EMP_PK |     1 |     7 |     2 |
    |*  2 |   TABLE ACCESS FULL| EMP    |     1 |     7 |     2 |
    -------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
      1a - filter("E1"."N2"= (SELECT /*+ FULL ("E2") */ "E2"."N1" FROM
                  "EMP" "E2" WHERE "E2"."N2"=:B1))
      1b - access("E1"."N1"=TO_NUMBER(:B1))
       2 - filter("E2"."N2"=:B1)
    
    

    And if your query was supposed to access the table by rowid then the FILTER operation and all its child rows would be indented one more place to the right as the FILTER would be driving the table access.

    • Tanel Poder says:

      Ok (3 years have passed apparently :) – looks like there’s a difference in the pushed subquery execution order depending on the access path to the parent query’s data. When it’s an index access, like you explained, the subquery gets accessed, but when it’s a full table scan, then Oracle behaves like I explained (but yep, the execution plan in the original post does have index in it, so my explanation is about a full table scan access path, which I erraneously generalized to index path too). Anyway, check this example below. I have written the query so that the filter on the parent query block’s table doesn’t let any rows through and so the plan execution doesn’t even go to execute the subquery (as seen from Starts column and also other tricks like SQL trace or rowsource dtracing etc) as there’s no need to further filter zero rows:

      SELECT empno FROM emp WHERE ename LIKE 'NONEXISTENT%' AND empno =
      (SELECT empno FROM emp WHERE ename = 'KING')
      
      ----------------------------------------------------------------------------------------------------------
      | Id  | Operation          | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| A-Rows |   A-Time   | Buffers |
      ----------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT   |      |      1 |        |       |     4 (100)|      0 |00:00:00.01 |       6 |
      |*  1 |  TABLE ACCESS FULL | EMP  |      1 |      1 |    10 |     2   (0)|      0 |00:00:00.01 |       6 |
      |*  2 |   TABLE ACCESS FULL| EMP  |      0 |      1 |    10 |     2   (0)|      0 |00:00:00.01 |       0 |
      ----------------------------------------------------------------------------------------------------------
      
         1 - filter("ENAME" LIKE 'NONEXISTENT%' AND "EMPNO"= (SELECT "EMPNO"
                    FROM "EMP" "EMP" WHERE "ENAME"='KING'))
         2 - filter("ENAME"='KING')
      
      • Tanel Poder says:

        Note that this example above is from a non-Exadata system (zero Starts in line #2). On exadata with cell_offload_processing = FALSE you’d also get zero Starts, but with cell_offload_processing = TRUE you get one execution (to materialize the scalar subquery result for sending in to the main table scan as a storage() predicate). So there sure seems to be some child execution order-related “IF-THEN-ELSE” logic in the parent step…

      • Tanel,

        I think it’s worth checking if what you’ve done here is a special case.
        You’ve used the EMP table twice, and Oracle has produced something that may be a generic plan structure where part of the plan is optional.

        If your subquery were to a different table, rather than EMP, then perhaps you would see a START in line 2. As it is there may be special code that says “there is no point in doing a full tablescan of EMP in line 2 to identify rows where empname = ‘KING’ when line 1 is a full scan of EMP”. (I’m assuming that there’s a PK on empno when I say that, by the way). Technically (with the PK in place) the query could collapse to the constradiction “select where ename like ‘NONEXISTANT%’ and ename = ‘KING'” – but the optimizer doesn’t do everything that a person could.

        • Pavan Kumar says:

          Hi Jonathan/Tanel,

          It’s an good example with understanding with access/filter and FTS ….from both of the examples.

          Impressive.. !!

  10. @Narendra

    Narenda.

    I’ve just remembered that I wrote a short note about the indentation bug a while ago at http://jonathanlewis.wordpress.com/2008/01/10/filter-plan-error/

Leave a Reply

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

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong>