Non-execution plans

It’s a funny feature of execution plans that they don’t need to execute in their entirety. There are many reasons why this can be so, but the underlying reason comes from the strategy of sharing SQL.

In an ideal system, optimization is done once per unique SQL statement, even when the statement involves bind variables. This means the optimizer has to find a single execution path that will work even when the bound values change.

As a consequence, the optimizer can produce a plan that gives the run-time engine some leeway in bypassing redundant stages of execution. (Hence the discusion in the article on my website: When 2 + 2 = 5).

Here’s a little example that applies to partitioned tables – run under 9.2.  The script creates a partitioned table, then queries it using a bind variable as an input to compare with the partitioning column. The optimizer produces a plan which is “single partition by index”, with the start and stop partitions shown as key/key, indicating that the specific partition can only be known at run-time.

But if you supply a bound value that is above the known high-value for the table, then the plan simply does not run beyond the ‘single partition’ stage. The script demonstrates this by running the query with the containing tablespace offline.

rem
rem	Create the table and index; gather stats
rem	and take the tablespace offline
rem         

create table pt_range (
	id	not null,
	grp,
	small_vc,
	padding
)
partition by range(id) (
	partition p200 values less than (200),
	partition p400 values less than (400)
)
as
select
	rownum				id,
	trunc(rownum/50)		grp,
	to_char(trunc(rownum/20))	small_vc,
	rpad('x',100)			padding
from
	all_objects
where
	 rownum <= 350;  

alter table pt_range
add constraint pt_pk primary key (id) using index local;  

begin
	dbms_stats.gather_table_stats(
		ownname		 => user,
		tabname		 =>'PT_RANGE',
		cascade		 => true,
		estimate_percent => null,
		granularity      => 'ALL',
		method_opt 	 => 'for all columns size 1'
	);
end;         

alter tablespace users offline;         

rem
rem	500 is above the current high value
rem	Explain the plan, then execute it
rem         

variable v_id number
exec :v_id := 500         

explain plan for
select
	grp, small_vc
from
	pt_range
where
	id = :v_id
;         

select * from table(dbms_xplan.display);         

select
	grp, small_vc
from
	pt_range
where
	id = :v_id
;         

alter tablespace users online;         

The end result of the test – from the ‘explain plan’ onwards is as follows:

       

Explained.       

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------       

--------------------------------------------------------------------------------------------
| Id | Operation                          |  Name    | Rows | Bytes | Cost | Pstart| Pstop |
--------------------------------------------------------------------------------------------
|  0 | SELECT STATEMENT                   |          |    1 |    10 |    1 |       |       |
|  1 |  PARTITION RANGE SINGLE            |          |      |       |      |   KEY |   KEY |
|  2 |   TABLE ACCESS BY LOCAL INDEX ROWID| PT_RANGE |    1 |    10 |    1 |   KEY |   KEY |
|* 3 |    INDEX UNIQUE SCAN               | PT_PK    |    1 |       |      |   KEY |   KEY |
--------------------------------------------------------------------------------------------       

Predicate Information (identified by operation id):
---------------------------------------------------       

   3 - access("PT_RANGE"."ID"=TO_NUMBER(:Z))       

Note: cpu costing is off       

16 rows selected.       

no rows selected       

Tablespace altered.       

The ’16 rows returned’ is from the select against the plan table. The ‘no rows returned’ is from the query – it didn’t fail, even though the tablespace was offline. Remember this when next you are working out what happened at run-time – some parts of the plan you can see may never have executed.