I knew I had an example somewhere, but it's not currently published. Here's a
12.2 plan showing Join filters and a partition filter:
---------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)|
Time | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 38 | 452 (4)|
00:00:01 | | | | | |
| 1 | SORT AGGREGATE | | 1 | 38 | |
| | | | | |
| 2 | PX COORDINATOR | | | | |
| | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10002 | 1 | 38 | |
| | | Q1,02 | P->S | QC (RAND) |
| 4 | SORT AGGREGATE | | 1 | 38 | |
| | | Q1,02 | PCWP | |
|* 5 | HASH JOIN | | 24500 | 909K| 452 (4)|
00:00:01 | | | Q1,02 | PCWP | |
| 6 | JOIN FILTER CREATE | :BF0001 | 5 | 30 | 2 (0)|
00:00:01 | | | Q1,02 | PCWP | |
| 7 | PART JOIN FILTER CREATE| :BF0000 | 5 | 30 | 2 (0)|
00:00:01 | | | Q1,02 | PCWP | |
| 8 | PX RECEIVE | | 5 | 30 | 2 (0)|
00:00:01 | | | Q1,02 | PCWP | |
| 9 | PX SEND BROADCAST | :TQ10000 | 5 | 30 | 2 (0)|
00:00:01 | | | Q1,00 | P->P | BROADCAST |
| 10 | PX BLOCK ITERATOR | | 5 | 30 | 2 (0)|
00:00:01 | | | Q1,00 | PCWC | |
|* 11 | TABLE ACCESS FULL | T3 | 5 | 30 | 2 (0)|
00:00:01 | | | Q1,00 | PCWP | |
|* 12 | HASH JOIN | | 343K| 10M| 447 (4)|
00:00:01 | | | Q1,02 | PCWP | |
| 13 | JOIN FILTER CREATE | :BF0002 | 70 | 420 | 2 (0)|
00:00:01 | | | Q1,02 | PCWP | |
| 14 | PX RECEIVE | | 70 | 420 | 2 (0)|
00:00:01 | | | Q1,02 | PCWP | |
| 15 | PX SEND BROADCAST | :TQ10001 | 70 | 420 | 2 (0)|
00:00:01 | | | Q1,01 | P->P | BROADCAST |
| 16 | PX BLOCK ITERATOR | | 70 | 420 | 2 (0)|
00:00:01 | | | Q1,01 | PCWC | |
| 17 | TABLE ACCESS FULL | T2 | 70 | 420 | 2 (0)|
00:00:01 | | | Q1,01 | PCWP | |
|* 18 | HASH JOIN | | 343K| 8708K| 441 (3)|
00:00:01 | | | Q1,02 | PCWP | |
| 19 | JOIN FILTER CREATE | :BF0003 | 70 | 420 | 2 (0)|
00:00:01 | | | Q1,02 | PCWP | |
| 20 | TABLE ACCESS FULL | T1 | 70 | 420 | 2 (0)|
00:00:01 | | | Q1,02 | PCWP | |
| 21 | JOIN FILTER USE | :BF0001 | 343K| 6699K| 436 (2)|
00:00:01 | | | Q1,02 | PCWP | |
| 22 | JOIN FILTER USE | :BF0002 | 343K| 6699K| 436 (2)|
00:00:01 | | | Q1,02 | PCWP | |
| 23 | JOIN FILTER USE | :BF0003 | 343K| 6699K| 436 (2)|
00:00:01 | | | Q1,02 | PCWP | |
| 24 | PX BLOCK ITERATOR | | 343K| 6699K| 436 (2)|
00:00:01 |:BF0000|:BF0000| Q1,02 | PCWC | |
|* 25 | TABLE ACCESS FULL | T4 | 343K| 6699K| 436 (2)|
00:00:01 |:BF0000|:BF0000| Q1,02 | PCWP | |
---------------------------------------------------------------------------------------------------------------------------------------
Operation 7 shows the partition filter due to t3 being created, operations24/25
show it appearing in the pstart/pstop
Operation 6 shows the join filter due to t3 being created, operation 23 shows
it being applied (actually during) the tablescan of the selected partitions
And here's the filter predicate being used during the tablescan.
25 -
filter(SYS_OP_BLOOM_FILTER_LIST(SYS_OP_BLOOM_FILTER(:BF0003,"T4"."ID1"),SYS_OP_BLOOM_FILTER(:BF0002,"T4"."ID2"),SYS_OP_B
LOOM_FILTER(:BF0001,"T4"."ID3")))
Apart from the problem of Bloom filters not appearing in plans, another little
issue with Bloom filters in earlier versions of Oracle is that the BF numbers
in the "create" lines don't always match the BF number where the filter is
used. Sometimes you just have to interpret the numbersfrom the SQL Monitor
report.
Regards
Jonathan Lewis
________________________________________
From: oracle-l-bounce@xxxxxxxxxxxxx <oracle-l-bounce@xxxxxxxxxxxxx> on behalf
of Jaromir D.B.Nemec <jaromir@xxxxxxxxxxxx>
Sent: 16 March 2018 16:07:53
To: 'Toon Koppelaars'; 'ORACLE-L'
Subject: RE: Bloom Filter Partition Pruning
Hi Toon,
OK, I understood. So in theory a false positive can happen (a partition is
scanned, but it contains no key) – but due to the practical number of
partitions this will be very rare.
Kind Regards,
Jaromir Nemec
http://www.db-nemec.com
Tel +436764039288
From: Toon Koppelaars [mailto:toon@xxxxxxxxxxx]
Sent: Freitag, 16. März 2018 11:10
To: jaromir nemec <jaromir@xxxxxxxxxxxx>
Subject: Re: Bloom Filter Partition Pruning
I cannot find this in the docs, but it is how it works.
This of course requires that the join-column is driving the partition-id in the
big table.
If that's not the case, you'll never get BF partition pruning.
On Fri, Mar 16, 2018 at 11:00 AM, jaromir nemec
<jaromir@xxxxxxxxxxxx<mailto:jaromir@xxxxxxxxxxxx>> wrote:
Hallo Toon,
thanks very much for the explanation, it makes total sense.
The only additional question I have is, is there some documentation of
this bahavior, or is it the *simplest possible explanation* that confirm
with the observation.
Thanks,
Jaromir
Normal BF usage hashes the column-values and sets bits based on these
hashes in the BF.
BF partition pruning usage of BF works differently:
- The column-values (of the smaller table) are first fed into the
function that produces the partition-id of the partition into which this
column-value would have been stored in the bigger table.
- It then hashes this partition-id and uses this hash to set bits in the
BF.
Then upon scanning the big table:
- Before it starts scanning a partition, it hashes the partition-id and
checks whether the bit is set in the BF
- If set: continue scanning the partition.
- If not set: skip this partition.
