Database Reference
In-Depth Information
Figure 13-6.
Representation of a
PARTITION RANGE SINGLE
operation
As the output of the following query shows, the partition number in the
Pstart
and
Pstop
columns matches the
value in the
partition_position
column in the
user_tab_partitions
view:
SQL> SELECT partition_name
2 FROM user_tab_partitions
3 WHERE table_name = 'T'
4 AND partition_position = 31;
PARTITION_NAME
--------------
T_3_JUL_2014
Whenever a bind variable is used in a restriction, the query optimizer is no longer able to determine which
partitions need to be accessed at parse time. In such cases, partition pruning is performed at runtime. The execution
plan doesn't change, but the values of the
Pstart
and
Pstop
columns are set to
KEY
. This indicates that partition
pruning occurs, but that at parse time, the query optimizer doesn't know which partition contains relevant data:
SELECT * FROM t WHERE n1 = :n1 AND d1 = to_date(
:d1
,'YYYY-MM-DD')
----------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | PARTITION RANGE SINGLE| | 1 |
KEY
|
KEY
|
|* 2 | TABLE ACCESS FULL | T | 1 |
KEY
|
KEY
|
----------------------------------------------------------------
2 - filter(("D1"=TO_DATE(:D1,'YYYY-MM-DD') AND "N1"=:N1))
