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
 
 
Search WWH ::




Custom Search