When working with big databases, speed matters. Oracle uses many smart tricks to make things faster. One of these tricks is called the Bloom filter. In this post, I’ll explain what it is and how it helps Oracle database run queries faster — in simple terms!
The Problem: Big Tables and Slow Joins
Let’s say we have two tables:
- One big table: SALES (millions of rows)
- One small table: CUSTOMERS (a few thousand rows)
And you run this query:
SELECT s.*, c.country
FROM sales s JOIN customers c
ON s.customer_id = c.customer_id
WHERE c.country = 'Switzerland';
The Optimizer needs to:
- Read the
CUSTOMERStable - Join it with
SALES - Filter for
country = 'Switzerland'
This join can be expensive if SALES is huge.
But joining big tables can be slow.
How Bloom Filters Help
The Bloom filter is like a super-fast “maybe list.”
It helps Oracle quickly skip rows in SALES that won’t match with CUSTOMERS.
Here’s how:
- Oracle first reads
CUSTOMERSand creates a Bloom filter using thecustomer_idvalues. - Then, as Oracle scans
SALES, it checks eachcustomer_idusing the Bloom filter. - If there’s no chance of a match, Oracle skips the row — no join needed!
This can greatly reduce the amount of data Oracle has to process.
Let’s look at a query that might trigger a Bloom filter:
SELECT /*+ USE_HASH(c s) FULL(s) FULL(c) PARALLEL(s 4) PARALLEL(c 4) */
s.sale_id, s.amount, c.country
FROM customers c
JOIN sales s
ON c.customer_id = s.customer_id
WHERE c.country = 'Germany';
Explanation of the hint:
USE_HASH: use a hash join (Bloom filters are only used with hash joins)FULL: use full table scansPARALLEL: run in parallel (Bloom filters are mostly used in parallel execution)
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost | |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | | |
| 1 | PX COORDINATOR | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | | | | |
| 3 | HASH JOIN | | | | | |
| 4 | PX RECEIVE | | | | | |
| 5 | PX SEND BROADCAST | :TQ10000 | | | | |
| 6 | TABLE ACCESS FULL | CUSTOMERS | | | | |
| 7 | PX BLOCK ITERATOR | | | | | |
| 8 | TABLE ACCESS FULL | SALES | | | | |
------------------------------------------------------------------------------------------------
Predicate Information:
----------------------
- bloom filter predicate on SALES.CUSTOMER_ID
Look for “bloom filter predicate” or:
BLOOM FILTER CREATEBLOOM FILTER USE
You don’t need to turn on Bloom filters — Oracle does it when it’s helpful.
Use hash joins + parallel to make it more likely Oracle will apply them.
Always check your execution plan to see what’s happening behind the scenes.
Oracle DBA Insights 