Database Reference
In-Depth Information
PARTITION RANGE SUBQUERY
In the previous sections, all restrictions used for partition pruning are based on literals or bind variables. However,
it's not uncommon for restrictions to actually be join conditions. Whenever a join is based on a partition key, not
only is the query optimizer not always able to take advantage of partition pruning, but in some situations it's also not
sensible to do so. To choose the execution plan with the lowest cost, the query optimizer has to choose between three
strategies.
■
Note
Chapter 14 covers the join methods in detail.
The first strategy is to elude partition pruning. The following query (note that the
tx
table is a copy of the
t
table;
the only difference is that the
tx
table isn't partitioned) illustrates this, where no partition pruning on the
t
table is
performed. In fact, because operation 4 is a
PARTITION RANGE ALL
, operation 5 is processed for all partitions. In this
particular example, this execution plan is highly inefficient. This is especially the case given that the selectivity of the
query is strong:
SELECT * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND tx.id = 19
-----------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop |
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | |
|* 1 | HASH JOIN | | 1 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | | |
|* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | |
| 4 |
PARTITION RANGE ALL
| | 1 |
1
|
48
|
| 5 | TABLE ACCESS FULL | T |
48
| 1 | 48 |
-----------------------------------------------------------------------
1 - access("TX"."D1"="T"."D1" AND "TX"."N1"="T"."N1")
3 - access("TX"."ID"=19)
This strategy is always available. Nevertheless, it could lead to poor performance if the selectivity of the join
condition isn't close to 1—or, in other words, in situations where partition pruning should be used.
The second strategy is to execute the join with the operation
NESTED LOOPS
and access the table, which the
partition pruning should occur on, as the second child. In fact, as discussed in Chapter 10, the
NESTED LOOPS
operation is a related-combine operation, and therefore, its first child controls the execution of the second child.
The following example shows such a situation. Note that the
PARTITION RANGE ITERATOR
operation and the values of
the
Pstart
and
Pstop
columns confirm that partition pruning takes place. According to the
Starts
column, a single