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 “first child first / recursive descent” 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 important to have the information 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 (‘… more what you’d call “guidelines” than actual rules.’ — Captain Barbossa (Pirates of the Carribean)) for reading execution 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 <= 3e3
)
select
	rownum			id,
	lpad(rownum,10,'0')	small_vc,
	rpad('x',100)		padding
from
	generator	v1,
	generator	v2
where
	rownum <= 1e4
;

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)