For many cases, Oracle determines the partitions to be accessed at compile time. Static partition pruning occurs if you use static predicates, except for the following cases:
Partition pruning occurs using the result of a subquery.
The optimizer rewrites the query with a star transformation and pruning occurs after the star transformation.
The most efficient execution plan is a nested loop.
These three cases result in the use of dynamic pruning.
If at parse time Oracle can identify which contiguous set of partitions is accessed, then the PSTART and PSTOP columns in the execution plan show the begin and the end values of the partitions being accessed. Any other cases of partition pruning, including dynamic pruning, show the KEY value in PSTART and PSTOP, optionally with an additional attribute.
The following is an example:
SQL> explain plan for select * from sales where time_id = to_date('01-jan-2001', 'dd-mon-yyyy');
Explained.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------
Plan hash value: 3971874201
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 673 | 19517 | 27 (8)| 00:00:01 | | |
| 1 | PARTITION RANGE SINGLE| | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |
|* 2 | TABLE ACCESS FULL | SALES | 673 | 19517 | 27 (8)| 00:00:01 | 17 | 17 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - filter("TIME_ID"=TO_DATE('2001-01-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
This plan shows that Oracle accesses partition number 17, as shown in the PSTART and PSTOP columns. The OPERATION column shows PARTITION RANGE SINGLE, indicating that only a single partition is being accessed. If OPERATION shows PARTITION RANGE ALL, then all partitions are being accessed and effectively no pruning takes place. PSTART then shows the very first partition of the table and PSTOP shows the very last partition.
An execution plan with a full table scan on an interval-partitioned table shows 1 for PSTART, and 1048575 for PSTOP, regardless of how many interval partitions were created.