Re: Bloom Filter Partition Pruning
Date: Tue, 20 Mar 2018 07:39:48 +0000
Message-ID: <MM1P123MB0988FB472849A8B06F0F00EAA5AB0_at_MM1P123MB0988.GBRP123.PROD.OUTLOOK.COM>
Correct, and in ideal circumstances, and depending on version of Oracle, you should see two separate indications of Bloom filter creation and Bloom filter usage. One usage would show in the PSTART/PSTOP, the other as a join filter use operation.
In earlier versions of Oracle the reporting of Bloom filters can be very poor, but sometimes you will see the use in the predicates sections as a a call to sys_op_bloom_filter(), or sys_op_bloom_filter_list() if multiple bloom filters can be applied at the same time.
I may have a blog item with a couple of examples.
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: 20 March 2018 06:56
To: 'Toon Koppelaars'
Cc: 'oracle-l-freelists'
Subject: RE: Bloom Filter Partition Pruning
Hi,
one last clarification:
My test with statistics from V$PX_TQSTAT shows, that while probing a partitioned table in a hash join a BF is used for both partition pruning AND the key filtering.
This would suggest, that with partitioned tables the described logic of BF based partition pruning is performed *additionally* to the standard way of BF based key filtering.
Is it a right interpretation?
Kind Regards,
Jaromir D.B. Nemec
http://www.db-nemec.com
From: Toon Koppelaars [mailto:toon_at_rulegen.com]
Sent: Freitag, 16. März 2018 08:18
To: jaromir_at_db-nemec.com
Cc: oracle-l-freelists <oracle-l_at_freelists.org>
Subject: Re: Bloom Filter Partition Pruning
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.
On Thu, Mar 15, 2018 at 11:35 PM, Jaromir D.B.Nemec <jaromir_at_db-nemec.com<mailto:jaromir_at_db-nemec.com>> wrote: Hi All,
I have basic understanding of the Bloom filter and the mechanism of the
-- http://www.freelists.org/webpage/oracle-lReceived on Tue Mar 20 2018 - 08:39:48 CET