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

Tanel Poder

2010/04/27

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 |
----------------------------------------------

NB! Check out my 2018/2019 online training classes: Advanced Oracle SQL Tuning training, Advanced Oracle Troubleshooting training and the new Practical Linux Performance & Application Troubleshooting training!