Database Reference
In-Depth Information
The execution plan is executed as follows:
•
Operations 3 and 4 access the
tx
table through the
tx_pk
index.
Based on the data returned by operation 3, operation 2 creates a memory structure (a bloom
•
filter) based on values from the columns used in the join condition (
tx.d1
and
tx.n1
).
Based on the memory structure created by operation 2, operation 5 is able to take advantage
•
of partition pruning and, therefore, is able to access only the partitions that contain relevant
data. In this case, a single partition is accessed (see the
Starts
column).
PARTITION RANGE MULTI-COLUMN
If the partition key is composed of several columns, it's important to observe what happens when a restriction isn't
defined for every column. The main question is, does the query optimizer take advantage of partition pruning? The
answer is, thanks to
multicolumn pruning
, yes. The goal of multicolumn pruning is quite simple: independently of
which columns a restriction is defined on, partition pruning always occurs.
Let's see this feature in action on the same test table we've used before. Because the partition key of the test table
is composed of two columns, there are two cases to consider: the restriction is applied to either the first column or the
second column. The following query is an example of the former:
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)
The following query is an example of the latter. Note that the
PARTITION RANGE MULTI-COLUMN
operation and
the value of the
Pstart
and
Pstop
columns confirm that partition pruning takes place; however, no information
is provided about which partitions are accessed:
SELECT * FROM t WHERE d1 = to_date('2014-07-19','YYYY-MM-DD')
----------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
| 1 |
PARTITION RANGE MULTI-COLUMN
| | 1 |
KEY(MC)
|
KEY(MC)
|
|* 2 | TABLE ACCESS FULL | T | 8 |KEY(MC)|KEY(MC)|
----------------------------------------------------------------------
2 - filter("D1"=TO_DATE(' 2014-07-19 00:00:00', 'syyyy-mm-dd hh24:mi:ss'))
