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 a fundamental requirement of reusing cursors.

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 (you may need to edit the code to use your preferred tablespace):

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   -- > comment to avoid WordPress format issue
;  

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 line ’16 rows selected’ in the above output comes from the select against the plan table. The line ‘no rows selected’ is from the query, which 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 the optimizer has created may have been bypassed on that particular execution.