Partition Pruning Tips

When using partition pruning, you should consider the following:
■ Partition Pruning Using DATE Columns
■ SQL Constructs to Avoid
Partition Pruning Using DATE Columns A common mistake occurs when using the Oracle
DATE datatype. An Oracle DATE datatype is not a character string, but is only
represented as such when querying the database; the format of the representation is
defined by the NLS setting of the instance or the session. Consequently, the same
reverse conversion has to happen when inserting data into a DATE filed or specifying a
predicate on such a field.
A conversion can either happen implicitly or explicitly by specifying a TO_DATE
conversion. Only a properly applied TO_DATE function guarantees that Oracle is
capable of uniquely determining the date value and using it potentially for static
pruning, which is especially beneficial for single partition access.
Rewriting the example of the "Basic Partition Pruning Techniques"
slightly, using the two-digit date format RR causes the predicate to become
non-deterministic, thus causing dynamic pruning:
SELECT SUM(amount_sold) day_sales
FROM sales,
WHERE time_id = '02-JAN-98';
The plan now should be similar to the following:

----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows| Bytes | Cost (%CPU) | Time | Pstart | Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 31 (100) | | | |
| 1 | SORT AGGREGATE | | 1 | 13 | | | | |
| 2 | PARTITION RANGE SINGLE| | 629 | 8177 | 31 (46) | 00:00:01 | KEY | KEY |
|* 3 | TABLE ACCESS FULL | SALES | 629 | 8177 | 31 (46) | 00:00:01 | KEY | KEY |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 – filter("TIME_ID"='02-JAN-98')

The keyword KEY for both columns means that partition pruning occurs at run-time.
See Oracle Database SQL Reference for details about the DATE datatype and Oracle
Database Globalization Support Guide for details about NLS settings and globalization
issues.
SQL Constructs to Avoid There are several cases when the optimizer cannot perform any
pruning. One of the most common reasons is when an operator is on top of a
partitioning column. This could be an explicit operator (for example, a function) or
even an implicit operator introduced by Oracle as part of the necessary data type
conversion for executing the statement. For example, consider the following query:
EXPLAIN PLAN FOR
SELECT SUM(quantity_sold)
FROM sales
WHERE time_id = TO_TIMESTAMP('1-jan'2000', 'dd-mon-yyyy');
Because time_id is of type DATE and Oracle needs to promote it to the TIMESTAMP
type to get the same datatype, this predicate is internally rewritten as:
TO_TIMESTAMP(time_id) = TO_TIMESTAMP('1-jan-2000', 'dd-mon-yyyy')
The explain plan for this statement is as follows:
--------------------------------------------------------------------------------------------
|Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 11 | 6 (17)| 00:00:01 | | |
| 1 | SORT AGGREGATE | | 1 | 11 | | | | |
| 2 | PARTITION RANGE ALL| | 10 | 110 | 6 (17)| 00:00:01 | 1 | 16 |
|*3 | TABLE ACCESS FULL | SALES | 10 | 110 | 6 (17)| 00:00:01 | 1 | 16 |
--------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(INTERNAL_FUNCTION("TIME_ID")=TO_TIMESTAMP('1-jan-2000',:B1))
15 rows selected

Posted in: Partitioning