Pruning using bloom filtering (Oracle Database 10g to 11g Change)

posted Sep 24, 2010, 9:10 AM by Sachchida Ojha
In Oracle Database 11g Partition Pruning now uses bloom filtering instead of subquery pruning. While subquery was activated on cost based decision and consumed internal (recursive) resources, pruning based on bloom filtering is activated all the time without consuming additional resources. Bloom filtering gives better performance with large sets.

The usage of the new feature pruning using bloom filtering can be identified in the explain plan by the following operator.

PART JOIN FILTER CREATE :BF0000

The use :BF0000 indicates pruning using bloom filtering.
Pruning Execution Plan

---------------------------------------------------------------------------
| Id  | Operation                             | Name      | Pstart| Pstop |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |           |       |       |
|   1 |  PX COORDINATOR                       |           |       |       |
|   2 |   PX SEND QC (RANDOM)                 | :TQ10002  |       |       |
|*  3 |    FILTER                             |           |       |       |
|   4 |     HASH GROUP BY                     |           |       |       |
|   5 |      PX RECEIVE                       |           |       |       |
|   6 |       PX SEND HASH                    | :TQ10001  |       |       |
|   7 |        HASH GROUP BY                  |           |       |       |
|*  8 |         HASH JOIN                     |           |       |       |
|   9 |          PART JOIN FILTER CREATE      | :BF0000   |       |       |
|  10 |           PX RECEIVE                  |           |       |       |
|  11 |            PX SEND PARTITION (KEY)    | :TQ10000  |       |       |
|  12 |             PX BLOCK ITERATOR         |           |       |       |
|  13 |              TABLE ACCESS FULL        | CUSTOMERS |       |       |
|  14 |          PX PARTITION HASH JOIN-FILTER|           |:BF0000|:BF0000|
|* 15 |           TABLE ACCESS FULL           | SALES     |:BF0000|:BF0000|
---------------------------------------------------------------------------
Comments