A couple of weeks ago at the Spring Conference of the Bulgarian Oracle User Group, Joel Goodman gave a great session on Bloom Filters and how the can improve the performance of hash joins in the Oracle Database especially in the In-Memory column store. At the end of that session the chap sitting in front of me turned around to show me an execution plan they got while testing Oracle Database In-Memory and posed the following question, “Where did the other bloom filter go?”
Below is a similar execution plan I was able to reproduce easily using the following query:
SELECT SUM(lo_extendedprice * lo_discount) revenue
FROM lineorder l,
WHERE l.lo_orderdate = d.d_datekey
AND l.lo_discount BETWEEN 2 AND 3
AND l.lo_quantity < 24
AND d.d_date='December 24, 2015';
Usually, it’s easy to identify bloom filters in the execution plan. They appear in two places, at creation time and again when it is applied. In the plan above, you will see a good example of this, bloom filter BF0001. Bloom filter BF0001 was created on line 4 after the scan of the DATE_DIM table on line 5. It was then used on line 6 as part of the scan of the LINEORDER table.
Now you might be wondering why Oracle came up with two bloom filters in this case, BF000 and BF0001 when there was only 1 join in the query and why wasn’t bloom filter BF000 used?
We are actually using both bloom filters, to do two different things in this plan, partition pruning (BF000) and converting the join to a filter operation (BF0001). The use of a bloom filter to do partition pruning is commonly referred to as bloom pruning.
Although you may not have seen the bloom filter BF000 being used in the plan at first glance (it’s second occurrence is not visible in the NAME column), it’s actually used on line 7, ‘PARTITION RANGE (JOIN FILTER)’, to access only a limited number of partitions in the LINEORDER table. How do I know that?
Lets look at a more detailed version of the execution plan so you can see what happened to BF000.
In the more detailed version of the plan you will see extra columns including the PARTITION_START and PARTITION_STOP columns. This is where the BF000 filter went. BF000 in the PARTITION_START and PARTITION_STOP columns indicates the bloom filter was used to limit the number of partitions scanned in the LINEORDER table for this query.
Bloom pruning is possible in this case as the LINEORDER table is range partitioned on the join column LO_ORDERDATE.