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 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.
…quotes should be > (greater than) characters above…
@Marcus Mönnig
select (select empno from emp where empno between -1 and 1) empno, deptno from emp;
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)
@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.
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 :)
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.
@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.
@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.
@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/