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

Last updated on AUGUST 25, 2014

Applies to:

Oracle Exadata Storage Server Software - Version 12.1.1.1.0 and later
Oracle Database - Enterprise Edition - Version 12.1.0.1 to 12.1.0.2 [Release 12.1]
Information in this document applies to any platform.

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

Sign In with your My Oracle Support account

Don't have a My Oracle Support account? Click to get started

My Oracle Support provides customers with access to over a
Million Knowledge Articles and hundreds of Community platforms