Exadata: Bloom filter is not used for hash left/right/full outer joins (Doc ID 1919508.1)

Last updated on FEBRUARY 22, 2019

Applies to:

Symptoms

Query is not using Bloom Filter when using hash left/right/full outer joins

For example:

select /*+ use_hash(p, c) */ *
 from T_P partition (P_P1) p
inner join T_C c             >>>>>>>>>>>>>>> Inner Join or Simple Join
   on c.PID = p.ID
;

Plan hash value: 3307425026

-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     1 |   297 |     5  (20)| 00:00:01 |       |       |
|*  1 |  HASH JOIN                  |         |     1 |   297 |     5  (20)| 00:00:01 |       |       |
|   2 |   PART JOIN FILTER CREATE   | :BF0000 |     1 |   142 |     2   (0)| 00:00:01 |       |       |             >>>>>>>>>>>>>>> Bloom Filter is being created
|   3 |    PARTITION HASH SINGLE    |         |     1 |   142 |     2   (0)| 00:00:01 |     1 |     1 |
|   4 |     TABLE ACCESS FULL       | T_P     |     1 |   142 |     2   (0)| 00:00:01 |     1 |     1 |
|   5 |   PARTITION HASH JOIN-FILTER|         |     1 |   155 |     2   (0)| 00:00:01 |:BF0000|:BF0000|
|   6 |    TABLE ACCESS FULL        | T_C     |     1 |   155 |     2   (0)| 00:00:01 |:BF0000|:BF0000|
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

  1 - access("C"."PID"="P"."ID")
========================================================================
select /*+ use_hash(p, c) */ *
 from T_P partition (P_P1) p
left outer join T_C c                    >>>>>>>>>>>>>>>>> Left Outer Join is used
   on c.PID = p.ID
;

-----------------------------------------------------------------------------------------------
| Id  | Operation              | Name | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
-----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT       |      |     1 |   297 |     5  (20)| 00:00:01 |       |       |
|*  1 |  HASH JOIN OUTER       |      |     1 |   297 |     5  (20)| 00:00:01 |       |       |
|   2 |   PARTITION HASH SINGLE|      |     1 |   142 |     2   (0)| 00:00:01 |     1 |     1 |
|   3 |    TABLE ACCESS FULL   | T_P  |     1 |   142 |     2   (0)| 00:00:01 |     1 |     1 |
|   4 |   PARTITION HASH ALL   |      |     1 |   155 |     2   (0)| 00:00:01 |     1 |     2 |
|   5 |    TABLE ACCESS FULL   | T_C  |     1 |   155 |     2   (0)| 00:00:01 |     1 |     2 |
-----------------------------------------------------------------------------------------------
1 - access("C"."PID"(+)="P"."ID")

Bloom Filter is not used when Left Outer Join is used

Changes

 None

Cause