Oracle Scratchpad

October 30, 2006

Non-execution plans

Filed under: CBO,Execution plans,Partitioning — Jonathan Lewis @ 4:15 pm GMT Oct 30,2006

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.

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

create table pt_range (
	id	not null,
partition by range(id) (
	partition p200 values less than (200),
	partition p400 values less than (400)
	rownum				id,
	trunc(rownum/50)		grp,
	to_char(trunc(rownum/20))	small_vc,
	rpad('x',100)			padding
	 rownum <= 350   -- > comment to avoid WordPress format issue

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

		ownname		 => user,
		tabname		 =>'PT_RANGE',
		cascade		 => true,
		estimate_percent => null,
		granularity      => 'ALL',
		method_opt 	 => 'for all columns size 1'

alter tablespace users offline;         

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

variable v_id number
exec :v_id := 500         

explain plan for
	grp, small_vc
	id = :v_id

select * from table(dbms_xplan.display);         

	grp, small_vc
	id = :v_id

alter tablespace users online;         

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



| 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 selected’ is from the select against the plan table. The ‘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.


  1. Jonathan –

    Fascinating result! How did you first come across this?


    Comment by Paul Vallee — October 30, 2006 @ 7:02 pm GMT Oct 30,2006 | Reply

  2. Paul
    It’s general techology that has been around in one form or another since 7.2.3 – a requirement of partition elimination in partition views. It’s simply a case of recognising new variants of the concept.

    Try the query:

    select * from pt_range where id > 600;

    You find a start/stop key of invalid – just another example of the same thing.

    Comment by Jonathan Lewis — October 30, 2006 @ 7:21 pm GMT Oct 30,2006 | Reply

  3. Very interesting.

    I just reproduce it on


    Comment by Mihajlo Tekic — October 30, 2006 @ 9:17 pm GMT Oct 30,2006 | Reply

  4. This seems to me to be a reasonable argument in support of not specifying “values less than (maxvalue)” unless it is absolutely required. I get a general vibe that specifying MAXVALUE is quite widespread in rolling date partitioning and it has always given me an uncomfortable feeling because of the need to split the top partition instead of just adding a new one — with the syntax being more verbose and more operations on the data dictionary being required it tends to rub me the wrong way to see it done. Now here’s a real stick to beat people with.

    “Offlining” a tablespace is a nice trick to demonstrate that a segment wasn’t accessed. I used it with Oracle support once to demonstrate that fast refresh of a materlialized view can be based entirely on the MV log without any reference to the master table itself. It’s funny how absolutely convinced they were that the master table was needed until I sent them that demonstration :)

    I think that it’s also worth noting that check constraints can operate in the same way as the partition range — a constraint of “id < 400″ allows the segment to not be accessed when a predicate of “id = 500″ is applied to a table scan. Demonstrable by the same offlining of a TS, of course.

    Comment by David Aldridge — October 31, 2006 @ 4:57 pm GMT Oct 31,2006 | Reply

  5. […] What I particularly like about this demonstration is the way that it shows both regular check constraints and partition definitions acting together — as Jonathan Lewis demonstrated here the partitioning scheme acts effectively as a series of check constraints on the individual partitions that allow table access to be avoided when it can be deduced that a predicate does not resolve to any partitions at all. […]

    Pingback by Partition Pruning Prevented by Functions on Predicates « The Oracle Sponge — November 22, 2006 @ 6:07 pm GMT Nov 22,2006 | Reply

  6. […] just another example of a “Non-execution plan”. At run-time, when Oracle hits the filter at line 2 it decides whether or not to run the sub-plan […]

    Pingback by Conditional SQL « Oracle Scratchpad — January 25, 2007 @ 1:53 am GMT Jan 25,2007 | Reply

  7. On 11G tablespace online offline trick is not working I think they changed the way it works

    Comment by coskan — May 1, 2009 @ 11:01 am BST May 1,2009 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by