Database Reference
In-Depth Information
The
PARTITION RANGE ITERATOR
operation is also used when a restriction is based on the leading part of the
partition key only. The following query illustrates this, where the restriction is applied to the first column of the
partition key. Note that partition 37 is accessed as well. This is because rows that have the value of the
n1
column
equal to 3 would be stored in that partition if the
d1
column had a value later than the 31st of December, 2014:
SELECT * FROM t WHERE n1 = 3
------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 | PARTITION RANGE ITERATOR| | 1 | 25 | 37 |
|* 2 | TABLE ACCESS FULL | T | 13 | 25 | 37 |
------------------------------------------------------------------
2 - filter("N1"=3)
As the name of this operation implies, it works only with a continuous range of partitions. When a noncontinuous
range is used, the operation presented in the next section comes into play.
PARTITION RANGE INLIST
If a restriction is based on one or several
IN
conditions that are composed of more than one element, a specific
operation,
PARTITION RANGE INLIST
, appears in the execution plan. With this operation, the
Pstart
and
Pstop
columns don't give precise information about which partitions are accessed. Instead, they show the value
KEY(I)
.
This indicates that partition pruning occurs separately for every value in the
IN
condition. In addition, the
Starts
column shows how many partitions are accessed (in this case, two):
SELECT * FROM t WHERE n1 IN (1,3) AND d1 = to_date('2014-07-19','YYYY-MM-DD')
----------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
----------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 |
PARTITION RANGE INLIST
| | 1 |
KEY(I)
|
KEY(I)
|
|* 2 | TABLE ACCESS FULL | T | 2 |KEY(I) |KEY(I) |
----------------------------------------------------------------
2 - filter(("D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
INTERNAL_FUNCTION("N1")))
In this specific case, based on the
WHERE
clause, you can infer that only partitions 7 and 31 are accessed.
Figure
13-8
illustrates this.
