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 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.

8 Comments »

  1. Jonathan -

    Fascinating result! How did you first come across this?

    Paul

    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 8.1.7.4

    –Mihajlo

    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 GMT May 1,2009 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

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

WordPress.com Logo

You are commenting using your WordPress.com 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 )

Google+ photo

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

Connecting to %s

Theme: Rubric. Get a free blog at WordPress.com

Follow

Get every new post delivered to your Inbox.

Join 4,422 other followers