Partition Pruning – Some Examples (Knives Out)

Following on from my last post where I looked at the use of partition pruning vs. the use of an index, I thought it might be worthwhile to just go through a few additional basic examples of where partition pruning can come into play.

I’ll also include a little example of how execution plans may not favour indexes as much with partitioned tables as the resultant cost of a Full Table Scan can be reduced significantly due to partition pruning.

All the examples use exactly the same tables as created in the previous post. For a full listing of the examples and resultant executions plans, look at this Partition Pruning Demo. You may want to open up the demo in a different browser window to make it easier to follow.

In Example 1:

SQL> select * from big_album_sales where release_date between ’01-JUN-2003′ and ’13-APR-2004′;

the CBO can determine that all the data can only belong in Partitions 3 and 4 (as only only years that need to be referenced are 2003 and 2004) and so not have to visit any of the other partitions. The PARTITION RANGE ITERATOR step clearly shows that it only has to visit the partitions in the range 3 – 4.

In Example 2:

SQL> select * from big_album_sales where release_date > ’01-JUN-2006′;

All the possible values must exist from partition 6 which stores all the 2006 data and onwards through to the last partition in the table which is partition 8. The PARTITION RANGE ITERATOR step clearly shows that it only has to visit the partitions in the range 6 – 8.

In Example 3:

SQL> select * from big_album_sales where release_date between ’01-JUN-2006′ and ’30-NOV-2006′ or release_date > ’14-JUN-2008′;

The only possible partitions the data can reside in are partition 6 or partition 8 (for the years 2006 or 2008). In this case, the CBO performs the PARTITION RANGE OR step which visits only these specific partitions.

In Example 4:

SQL> select * from big_album_sales where release_date is null;

All NULL values must reside in the last partition, as the table was created with the last partition having a MAXVALUE boundary. That being the case, the CBO need only visit the last partition (8) within the table to find all possible NULL values of RELEASE_DATE.

Because partition pruning can make performing a so-called Full Table Scan much more efficient as only those partitions with possible data need only be accessed, in some rare cases this can make the CBO appear to ignore reasonable indexes that may be used and accessed in an equivalent non-partitioned table.

Note that the data includes a series of RELEASE_DATE values but many of the dates actually have the same timestamp component as all the row values were determined and loaded in a very quick manner. As a result, the CBO considers there are actually 718 rows per distinct occurrence of a RELEASE_DATE value. This fact is significant when costing an equality predicate.

In Example 5:

SQL> select * from big_album_sales where release_date = ’01-JUN-2006′;

we’re only interested in the one specific date value but Oracle still favours the Full Table Scan (FTS). Note this value can only possibly exist in partition 6 associated with data from 2006 and so the FTS need only actually access partition 6. Oracle assumes it will retrieve approximately 718 rows which is too costly via the index as partition pruning can make the FTS relatively efficient. Note that the actual cost of the FTS is 303.

In Example 6:

SQL> select /*+ index(b) */ * from big_album_sales b where release_date = ’01-JUN-2006′;

a hint is used to force the use of the associated index on the RELEASE_DATE column. Note in this example, it’s actually a much cheaper option (there are far fewer consistent gets) as no rows are actually being retrieved but the cost of 722 is significantly greater than that of the FTS (303) as Oracle is expecting to retrieve and access 718 rows.

In Example 7:

SQL> select * from big_album_sales2 where release_date = ’01-JUN-2006′;

we run exactly the same statement but this time on the non-partitioned table and notice that the execution plan is totally different. Oracle is indeed using the index because in this example the FTS is so much more expensive as it needs to read the entire table and not just the one partition (or approximately 1/8 of the table) as it does with the partitioned table example.

Lots more on partitioning to come in the future …