Database Reference
In-Depth Information
Another case where the very same execution plan is used is when a restriction on the partition key is based on
an expression or function. For example, in the following query, one is added to the
n1
column, and the
d1
column is
modified through the
to_char
function:
SELECT * FROM t WHERE n1 + 1 = 4 AND to_char(d1,'YYYY-MM-DD') = '2014-07-19'
This means that to take advantage of partition pruning, not only should you have a restriction based on the
partition key, but you should also not apply an expression or function to it. If applying an expression is a must, as of
version 11.1 it's possible to choose a virtual column as partition key.
PARTITION RANGE EMPTY
A particular operation,
PARTITION RANGE EMPTY
, appears in execution plans when the query optimizer recognizes
that no partition is able to store processing-relevant data. For example, the following query is looking for data that
has no partition where it could be stored (for the
n1
column, the value 5 is out of range). It's also important to note
that not only are the
Pstart
and
Pstop
columns set to the value
INVALID
, but in addition, only operation 1 is executed
(consuming no resources at all because, basically, it's a no-op operation):
SELECT * FROM t WHERE
n1 = 5
AND d1 = to_date('2014-07-19','YYYY-MM-DD')
---------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
---------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 |
PARTITION RANGE EMPTY
| | 1 |
INVALID
|
INVALID
|
|* 2 | TABLE ACCESS FULL | T | 0 |INVALID|INVALID|
---------------------------------------------------------------
2 - filter(("D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND "N1"=5))
PARTITION RANGE OR
The type of pruning described in this section, the so-called
OR pruning
, is used for
WHERE
clauses that contain
disjunctive predicates (predicates combined by OR conditions) on the partition key. The following query is an
example of such a situation. When this type of pruning is used, the
PARTITION RANGE OR
operation appears in the
execution plan. Also note that the
Pstart
and
Pstop
columns are set to the value
KEY(OR)
. In the following example,
according to the
Starts
column, 18 partitions are accessed. There are 18 because although the restriction applied to
the
n1
column causes partitions 25 to 37 to be accessed, the restriction applied to the
d1
column causes partitions 1, 3,
15, 27, and 39 to be accessed (partition 1 is necessary in order to find out whether there are rows with the
n1
column
containing values less than 1):
SELECT * FROM t WHERE n1 = 3
OR
d1 = to_date('2014-03-06','YYYY-MM-DD')
------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 |
PARTITION RANGE OR
| | 1 |
KEY(OR)
|
KEY(OR)
|
|* 2 | TABLE ACCESS FULL| T | 18 |KEY(OR)|KEY(OR)|
------------------------------------------------------------
