Why it has HASH JOIN in this execution plan (explain plan)?

Question

I'm testing some Oracle statements and their execution plan and having struggle with this one (inner joining 2 table):

SELECT COUNT(*) 
FROM WF_TRANSITION T, 
     WF_VERSION_REQUEST_TYPE VRT 
WHERE T.FK_VS_REQUEST_TYPE_ID = VRT.VS_REQUEST_TYPE_ID + 0

Here's its execution plan:

Execution plan for above SQL statement

My question is why we have step 6 HASH JOIN, meanwhile we do NESTED LOOP before. I think this NESTED LOOP joined 2 table WF_TRANSITION and WF_VERSION_REQUEST_TYPE and no need HASH JOIN. Can anyone please explain this to me?

Chris Saxon Chris Saxon · Accepted Answer · 2019-08-20T10:01:36

You have an adaptive plan. The database will choose to do either a hash join OR nested loop based on the number of rows processed.

You can tell this by the statistics collector step. This is counting the rows flowing out of the scan on wf_version_reqeuest_types_pk.

If this number stays below a threshold, it'll use the nested loop. Above this it'll switch to a hash join.

To find out which it did, get the execution plan for the query. If you add the +ADAPTIVE option when using DBMS_XPlan, it'll show you which join was discarded with by prefixing these operations with -:

set serveroutput off

select /*+ gather_plan_statistics */*
from   hr.employees e
join   hr.departments d
on     e.department_id = d.department_id;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ROWSTATS LAST +ADAPTIVE'));

Plan hash value: 4179021502                                                                 

----------------------------------------------------------------------------------------    
|   Id  | Operation                     | Name              | Starts | E-Rows | A-Rows |    
----------------------------------------------------------------------------------------    
|     0 | SELECT STATEMENT              |                   |      1 |        |    106 |    
|  *  1 |  HASH JOIN                    |                   |      1 |    106 |    106 |    
|-    2 |   NESTED LOOPS                |                   |      1 |    106 |     27 |    
|-    3 |    NESTED LOOPS               |                   |      1 |        |     27 |    
|-    4 |     STATISTICS COLLECTOR      |                   |      1 |        |     27 |    
|     5 |      TABLE ACCESS FULL        | DEPARTMENTS       |      1 |     27 |     27 |    
|- *  6 |     INDEX RANGE SCAN          | EMP_DEPARTMENT_IX |      0 |        |      0 |    
|-    7 |    TABLE ACCESS BY INDEX ROWID| EMPLOYEES         |      0 |      4 |      0 |    
|     8 |   TABLE ACCESS FULL           | EMPLOYEES         |      1 |    107 |    107 |    
----------------------------------------------------------------------------------------    

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

   1 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      
   6 - access("E"."DEPARTMENT_ID"="D"."DEPARTMENT_ID")                                      

Note                                                                                        
-----                                                                                       
   - this is an adaptive plan (rows marked '-' are inactive)

Why it has HASH JOIN in this execution plan (explain plan)?

1 Answers