Example of showplan Output for Bloom Filters

The output of showplan indicates the use of bloom filters in the final plan.

Any use of bloom filters is indicated in showplan output. A single plan can contain multiple bloom filters. Each bloom filter is associated with a unique ID, represented in the output as "bv<id>".

An example of showplan output:

QUERY PLAN FOR STATEMENT 1 (at line 1).   
Optimized using Parallel Mode   
Optimized using the Abstract Plan in the PLAN clause.       
STEP 1           
The type of query is SELECT.                          
8 operator(s) under root              
| ROOT:EMIT Operator (VA = 8)          
|          
| | HASH VECTOR AGGREGATE Operator (VA = 7)          
| | GROUP BY          
| | Evaluate Grouped COUNT AGGREGATE.          
| | Evaluate Grouped SUM OR AVERAGE AGGREGATE.          
| | Using Worktable4 for internal storage.          
| | Key Count: 1          
| |          
| | | HASH JOIN Operator (VA = 6) (Join Type: Inner Join)          
| | | Using Worktable3 for internal storage.          
| | | Building pushdown bloom filter (bv6).
| | |  Key Count: 1          
| | |      
| | | | SCAN Operator (VA = 0)          
| | | | FROM TABLE          
| | | | Location          
| | | | L          
| | | | Table Scan.          
| | | | Forward Scan.          
| | | | Positioning at start of table.          
| | | | Using I/O Size 16 Kbytes for data pages.          
| | | | With LRU Buffer Replacement Strategy for data pages.          
| | |          
| | | | HASH JOIN Operator (VA = 5) (Join Type: Inner Join)          
| | | | Using Worktable2 for internal storage.          
| | | | Building pushdown bloom filter (bv5).          
| | | | Key Count: 1          
| | | |          
| | | | | SCAN Operator (VA = 1)          
| | | | | FROM TABLE          
| | | | | Customer          
| | | | | C          
| | | | | Table Scan.          
| | | | | Forward Scan.          
| | | | | Positioning at start of table.          
| | | | | Using I/O Size 16 Kbytes for data pages.          
| | | | | With LRU Buffer Replacement Strategy for data pages.          
| | | |          
| | | | | HASH JOIN Operator (VA = 4) (Join Type: Inner Join)          
| | | | | Using Worktable1 for internal storage.          
| | | | | Building pushdown bloom filter(bv4).          
| | | | | Key Count: 1          
| | | | |          
| | | | | | SCAN Operator (VA = 2)          
| | | | | | FROM TABLE          
| | | | | | Time         
| | | | | | T         
| | | | | | Table Scan.         
| | | | | | Forward Scan.          
| | | | | | Positioning at start of table.         
| | | | | | Using I/O Size 16 Kbytes for data pages.          
| | | | | | With LRU Buffer Replacement Strategy for data pages.          
| | | | |          |  
| | | | | RESTRICT Operator (VA = 4)          
| | | | | | Using pushdown bloom filter (bv4, bv5, bv6).          
| | | | | |          
| | | | | | | SCAN Operator (VA = 3)          
| | | | | | | FROM TABLE          
| | | | | | | Orders          
| | | | | | | O          
| | | | | | | Table Scan.          
| | | | | | | Forward Scan.          
| | | | | | | Positioning at start of table.          
| | | | | | | Using I/O Size 16 Kbytes for data pages.          
| | | | | | | With MRU Buffer Replacement Strategy for data pages.