Database Reference
In-Depth Information
partition is accessed. In this specific case, the following execution plan is, therefore, far more efficient than the one
used by the first strategy:
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 | NESTED LOOPS | | 1 | | |
| 2 | TABLE ACCESS BY INDEX ROWID| TX | 1 | | |
|* 3 | INDEX UNIQUE SCAN | TX_PK | 1 | | |
| 4 |
PARTITION RANGE ITERATOR
| | 1 |
KEY
|
KEY
|
|* 5 | TABLE ACCESS FULL | T |
1
| KEY | KEY |
-----------------------------------------------------------------------
3 - access("TX"."ID"=19)
5 - filter(("TX"."D1"="T"."D1" AND "TX"."N1"="T"."N1"))
This strategy performs well only if the number of rows returned by the first child of the
NESTED LOOP
operation
(in this case operation 2) is low. Otherwise, it's even possible that the same partition is accessed several times by the
second child (in this case operation 4).
The third strategy is to execute the join with the
HASH JOIN
or
MERGE JOIN
operation. No regular partition pruning
based on the join condition is possible, however, with these join methods. In fact, as discussed in Chapter 10, they're
unrelated-combine operations, and consequently, the two children are executed separately. In such cases, the query
optimizer can take advantage of another type of partition pruning, the
subquery pruning
. The idea here is to find out
with a recursive query which partitions of the second child should be accessed. For that purpose, the SQL engine
executes a recursive query (on the table accessed by the first child) to retrieve the columns used in the join condition
that maps to the partition keys of the second child. Then, by consulting the partition definitions of the second child
stored in the data dictionary, the partitions to be accessed by the second child are identified, and so it's possible to
scan only them. The following query shows an example of this. Note that the operation
PARTITION RANGE SUBQUERY
and the value of the
Pstart
and
Pstop
columns (
KEY(SQ)
) confirm that partition pruning takes place. According to the
Starts
column, a single partition is accessed:
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 SUBQUERY
| | 1 |
KEY(SQ)
|
KEY(SQ)
|
| 5 | TABLE ACCESS FULL | T |
1
|KEY(SQ)|KEY(SQ)|
-----------------------------------------------------------------------
1 - access("TX"."D1"="T"."D1" AND "TX"."N1"="T"."N1")
3 - access("TX"."ID"=19)
