Bloom filters
Hello - I have a problem with using a bloom filter to implement a join. It works for a small data set, but not for a larger set. In this example, I'm working in the SCOTT schema. As you know, a prereq for a bloom filter join is hash join and parallelism, and if I hint it it works:
orcl> alter table emp parallel (degree 2);
Table altered.
orcl> alter table dept parallel (degree 2);
Table altered.
orcl> select /*+ use_hash(emp dept) bloom join 2 */ ename,dname from emp natural join dept where sal=1000;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 2858815543
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 26 | 5 (20)| 00:00:01 | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 1 | 26 | 5 (20)| 00:00:01 | Q1,01 | P->S
|* 3 | HASH JOIN | | 1 | 26 | 5 (20)| 00:00:01 | Q1,01 | PCWP
| 4 | PX RECEIVE | | 1 | 13 | 2 (0)| 00:00:01 | Q1,01 | PCWP
| 5 | PX SEND BROADCAST | :TQ10000 | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | P->P
| 6 | PX BLOCK ITERATOR | | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | PCWC
|* 7 | TABLE ACCESS STORAGE FULL| EMP | 1 | 13 | 2 (0)| 00:00:01 | Q1,00 | PCWP
| 8 | PX BLOCK ITERATOR | | 4 | 52 | 2 (0)| 00:00:01 | Q1,01 | PCWC
|* 9 | TABLE ACCESS STORAGE FULL | DEPT | 4 | 52 | 2 (0)| 00:00:01 | Q1,01 | PCWP
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
7 - storage("EMP"."SAL"=1000)
filter("EMP"."SAL"=1000)
9 - storage(SYS_OP_BLOOM_FILTER(:BF0000,"DEPT"."DEPTNO"))
filter(SYS_OP_BLOOM_FILTER(:BF0000,"DEPT"."DEPTNO"))
orcl>
you see the bloom filter at id 9, and as a bonus it is offloadable. But if I tell the optimizer that the tables are a bit larger, then it doesn't work:
orcl>
orcl> exec dbms_stats.set_table_stats('scott','emp',numrows=>1000000000)
PL/SQL procedure successfully completed.
orcl> exec dbms_stats.set_table_stats('scott','dept',numrows=>1000000)
PL/SQL procedure successfully completed.
orcl> select /*+ use_hash(emp dept) bloom join 2 */ ename,dname from emp natural join dept where sal=1000;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 197865614
-----------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT
-----------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8928G| 211T| 26M(100)| 01:45:08 | |
| 1 | PX COORDINATOR | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 8928G| 211T| 26M(100)| 01:45:08 | Q1,01 | P->S
|* 3 | HASH JOIN | | 8928G| 211T| 26M(100)| 01:45:08 | Q1,01 | PCWP
| 4 | PX RECEIVE | | 1000K| 12M| 7 (72)| 00:00:01 | Q1,01 | PCWP
| 5 | PX SEND BROADCAST | :TQ10000 | 1000K| 12M| 7 (72)| 00:00:01 | Q1,00 | P->P
| 6 | PX BLOCK ITERATOR | | 1000K| 12M| 7 (72)| 00:00:01 | Q1,00 | PCWC
| 7 | TABLE ACCESS STORAGE FULL| DEPT | 1000K| 12M| 7 (72)| 00:00:01 | Q1,00 | PCWP
| 8 | PX BLOCK ITERATOR | | 35M| 442M| 8993 (100)| 00:00:03 | Q1,01 | PCWC
|* 9 | TABLE ACCESS STORAGE FULL | EMP | 35M| 442M| 8993 (100)| 00:00:03 | Q1,01 | PCWP
-----------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
9 - storage("EMP"."SAL"=1000)
filter("EMP"."SAL"=1000)
orcl>
it still doesn't give me a bloom filter if I force te join order:
orcl> select /*+ ordered use_hash(emp dept) bloom join 2 */ ename,dname from emp natural join dept where sal=1000;
no rows selected
Execution Plan
----------------------------------------------------------
Plan hash value: 4099459956
--------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Dis
--------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8928G| 211T| 26M(100)| 01:45:08 | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10001 | 8928G| 211T| 26M(100)| 01:45:08 | Q1,01 | P->S | QC (RA
|* 3 | HASH JOIN | | 8928G| 211T| 26M(100)| 01:45:08 | Q1,01 | PCWP |
| 4 | PX BLOCK ITERATOR | | 35M| 442M| 8993 (100)| 00:00:03 | Q1,01 | PCWC |
|* 5 | TABLE ACCESS STORAGE FULL | EMP | 35M| 442M| 8993 (100)| 00:00:03 | Q1,01 | PCWP |
| 6 | BUFFER SORT | | | | | | Q1,01 | PCWC |
| 7 | PX RECEIVE | | 1000K| 12M| 7 (72)| 00:00:01 | Q1,01 | PCWP |
| 8 | PX SEND BROADCAST | :TQ10000 | 1000K| 12M| 7 (72)| 00:00:01 | Q1,00 | P->P | BROADC
| 9 | PX BLOCK ITERATOR | | 1000K| 12M| 7 (72)| 00:00:01 | Q1,00 | PCWC |
| 10 | TABLE ACCESS STORAGE FULL| DEPT | 1000K| 12M| 7 (72)| 00:00:01 | Q1,00 | PCWP |
--------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("EMP"."DEPTNO"="DEPT"."DEPTNO")
5 - storage("EMP"."SAL"=1000)
filter("EMP"."SAL"=1000)
orcl>
Any idea why the optimizer refuses to use a bloom filter? Any insight will be gratefully received.
Edited by: JohnWatson on Dec 1, 2012 8:29 AM
I should have said, this is 11.2.0.3