Re: Bloom Filter Partition Pruning
Date: Tue, 20 Mar 2018 08:23:23 +0000
Message-ID: <MM1P123MB09881565C56257DEEB706D5AA5AB0_at_MM1P123MB0988.GBRP123.PROD.OUTLOOK.COM>
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_at_freelists.org <oracle-l-bounce_at_freelists.org> on behalf of Jaromir D.B.Nemec <jaromir_at_db-nemec.com> 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_at_rulegen.com]
Sent: Freitag, 16. März 2018 11:10
To: jaromir nemec <jaromir_at_db-nemec.com>
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_at_db-nemec.com<mailto:jaromir_at_db-nemec.com>> 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.
>
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 20 2018 - 09:23:23 CET