Database Reference
In-Depth Information
PARTITION RANGE AND
In some situations the query optimizer can take advantage of several of the pruning techniques described in the
previous sections. As an example, have a look at the following query:
SELECT * FROM tx, t WHERE tx.d1 = t.d1 AND tx.n1 = t.n1 AND t.n1 = 3 AND tx.n2 = 42
With such a SQL statement, the query optimizer should consider at least two pruning techniques:
t.n1 = 3 restriction:
Partition pruning based on the
-----------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 889 |
|* 1 | HASH JOIN | | 1 | | | 889 |
|* 2 | TABLE ACCESS FULL | TX | 1 | | | 403 |
| 3 | PARTITION RANGE ITERATOR| | 1 | 25 | 37 | 486 |
|* 4 | TABLE ACCESS FULL | T | 13 | 25 | 37 | 486 |
-----------------------------------------------------------------------------
1 - access("TX"."N1"="T"."N1" AND "TX"."D1"="T"."D1")
2 - filter(("TX"."N2"=42 AND "TX"."N1"=3))
4 - filter("T"."N1"=3)
tx.d1 = t.d1 AND tx.n1 = t.n1 join condition (to take
advantage of the tx.n2 = 42 restriction):
Partition pruning based on the
-----------------------------------------------------------------------------------
| Id | Operation | Name | Starts | Pstart| Pstop | Buffers |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | 963 |
|* 1 | HASH JOIN | | 1 | | | 963 |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 1 | | | 403 |
|* 3 | TABLE ACCESS FULL | TX | 1 | | | 403 |
| 4 | PARTITION RANGE JOIN-FILTER| | 1 |:BF0000|:BF0000| 560 |
|* 5 | TABLE ACCESS FULL | T | 15 |:BF0000|:BF0000| 560 |
-----------------------------------------------------------------------------------
1 - access("TX"."N1"="T"."N1" AND "TX"."D1"="T"."D1")
3 - filter("TX"."N2"=42)
5 - filter("T"."N1"=3)
From version 11.2 onward, the query optimizer can even take advantage of several pruning techniques at once.
This guarantees that the least number of partitions is accessed (compare the Starts column of the three cases).
The following example shows that when this type of pruning, called AND pruning , is used, the PARTITION RANGE AND
operation appears in the execution plan. Also note that the Pstart and Pstop columns are set to the value KEY(AP) .
In this specific case, the partition pruning is based on both the restriction ( t.n1 = 3 ) and the join condition
( tx.d1 = t.d1 AND tx.n1 = t.n1 ). For the join condition, notice that a bloom filter is created:
 
Search WWH ::




Custom Search