Bloom Filter Partition Pruning
Date: Thu, 15 Mar 2018 23:35:38 +0100
Message-ID: <16cd01d3bcad$f2463d90$d6d2b8b0$_at_db-nemec.com>
Hi All,
I have basic understanding of the Bloom filter and the mechanism of the
filtering the keys
and I was convinced, that the same is true for the Bloom filter partition
pruning. But recently
after investigating a query on a hash partitioned table I'm somehow confused
and need some clarification.
Let me illustrate it on a simple example
TABLEA is hash partitioned on the join column TABLEB is a simple table
The query ...
SELECT A.* FROM TABLEA A
WHERE ID IN (SELECT ID FROM TABLEB)
... leads to the following execution plan:
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop | ---------------------------------------------------------------------------- ---------------------------00:00:01 |:BF0000|:BF0000|
| 0 | SELECT STATEMENT | | 24242 | 1088K| 460 (1)|
00:00:01 | | | |* 1 | HASH JOIN RIGHT SEMI | | 24242 | 1088K| 460 (1)| 00:00:01 | | |
| 2 | PART JOIN FILTER CREATE | :BF0000 | 2 | 44 | 3 (0)|
00:00:01 | | | |* 3 | TABLE ACCESS FULL | TABLEB | 2 | 44 | 3 (0)| 00:00:01 | | |
| 4 | PARTITION HASH JOIN-FILTER| | 400K| 9375K| 456 (1)|
00:00:01 |:BF0000|:BF0000|
| 5 | TABLE ACCESS FULL | TABLEA | 400K| 9375K| 456 (1)|
So in the operation 2 the BF is created after reading all keys from the TABLEB and this BF is used in operation 4 to prune the partitions of the TABLEA.
This is exact the point of my doubt - this could work for LIST partitioning schema, where I can take all list partition keys (from the partition definition) and check them against the BF to see if a partition is relevant or not. But this will IMO never work for a HASH partitioning. Oracle will not scan a partition of a TABLEA and check all keys with the BF to see that no key matches and the partition can be pruned...
So my question is, how is the Bloom filter partition pruning implemented for
HASH and RANGE partitioning schema.
Does Oracle additionally to BF pass a list of partitions? I see in 10128
trace that the pruning works fine and I didn't encounter a false positive
(i.e. not pruned) partition, so I guess there will be some simple solution,
but a web search provided no explanation.
Kind Regards,
Jaromir D.B. Nemec
http://www.db-nemec.com
-- http://www.freelists.org/webpage/oracle-lReceived on Thu Mar 15 2018 - 23:35:38 CET