Plan Order

Plan Order:
The previous post reminded me of another (fairly special) case where the order of operations in an execution plan seems to be wrong according to the “traditional” strategy for reading execution plans. Here’s a simple select statement with its execution plan to demonstrate the point:

select
 small_vc
from
 t1
where
 exists (
  select null
  from f1
  where f1.id       = t1.id
  and f1.small_vc = t1.small_vc
 )
and
 exists (
  select null
  from f2
  where f2.id = 21
 )
;

------------------------------------------------------------------------------
| Id  | Operation             | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |       |     1 |    29 |    51   (2)| 00:00:01 |
|*  1 |  FILTER               |       |       |       |            |          |
|*  2 |   HASH JOIN RIGHT SEMI|       |     1 |    29 |    51   (2)| 00:00:01 |
|   3 |    TABLE ACCESS FULL  | F1    |    20 |   280 |     2   (0)| 00:00:01 |
|   4 |    TABLE ACCESS FULL  | T1    | 10000 |   146K|    48   (0)| 00:00:01 |
|*  5 |   INDEX UNIQUE SCAN   | F2_PK |     1 |    13 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( EXISTS (SELECT /*+ */ 0 FROM "F2" "F2" WHERE
              "F2"."ID"=21))
   2 - access("F1"."ID"="T1"."ID" AND "F1"."SMALL_VC"="T1"."SMALL_VC")
   5 - access("F2"."ID"=21)

The traditional strategy for reading this plan (recursively operate the child rows of each parent row in the order that they appear) would say: we scan table F1 and buildilng a hash table in memory, then scan table T1 probing the hash table to perform the hash semi join of line 2. For each row that survives the hash join, the filter operation at line 1 tells us to run the subquery against F2 to see if the row should be passed forward as an output of the select statement.
You might like to pause briefly at this point to convince yourself that this is the way we usually interpret the indentation of an execution plan.
It’s not what happens in this special case. Notice that the second subquery isn’t correlated – it need only run once for Oracle to decide whether or not it will return any data. As a side effect of this special case, the plan operates “upside down”. Here’s the same execution plan pulled from memory after enabling rowsource execution statistics. It’s implrtant to be aware that in my test case the data in F2 doesn’t have a row where id = 21.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------
SQL_ID  5bd1m72cz4awy, child number 0
-------------------------------------
select  small_vc from  t1 where  exists (   select null   from f1   where f1.id       = t1.id   and
f1.small_vc = t1.small_vc  ) and  exists (   select null   from f2   where f2.id = 21  )

Plan hash value: 1423735592

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation             | Name  | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------
|*  1 |  FILTER               |       |      1 |        |      0 |00:00:00.01 |       1 |       |       |          |
|*  2 |   HASH JOIN RIGHT SEMI|       |      0 |      1 |      0 |00:00:00.01 |       0 |   825K|   825K|          |
|   3 |    TABLE ACCESS FULL  | F1    |      0 |     20 |      0 |00:00:00.01 |       0 |       |       |          |
|   4 |    TABLE ACCESS FULL  | T1    |      0 |  10000 |      0 |00:00:00.01 |       0 |       |       |          |
|*  5 |   INDEX UNIQUE SCAN   | F2_PK |      1 |      1 |      0 |00:00:00.01 |       1 |       |       |          |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - access("F1"."ID"="T1"."ID" AND "F1"."SMALL_VC"="T1"."SMALL_VC")
   5 - access("F2"."ID"=21)

Look carefully at the starts column. Line 1 (filter) started once; line 5 (the second child to the filter) started once and returned no rows; line 2 (the first child to the filter) never started – it didn’t have to because by this point Oracle had already determined that any data it generated would be eliminated by the non-existence of a match from line 5.

Summary

There are a few special case plans where the normal “first child first” rule for reading execuetion plans doesn’t apply. The “constant subquery” introduces one of them.

Footnote:

If you want to repeat the experiment on different versions of Oracle, here’s the code to generate my test data:

create table t1
as
with generator as (
 select --+ materialize
  rownum  id
 from all_objects
 where rownum <= 3000
)
select
 rownum   id,
 lpad(rownum,10,'0') small_vc,
 rpad('x',100)  padding
from
 generator v1,
 generator v2
where
 rownum <= 10000
;

create table f1 as select * from t1 where id <= 20;
create table f2 as select * from t1 where id <= 20;

alter table f1 add constraint f1_pk primary key(id);
alter table f2 add constraint f2_pk primary key(id);

-- collect stats (compute, no histograms)

DIGITAL JUICE