sqloracledbeaver

ORACLE Query Execution plan Understanding JOIN

For underlying execution plan in oracle , I wanted to inquire about the joins. How is it actually working , As I have the following query

SELECT *
FROM Schema.T1
JOIN 
  (SELECT * FROM Schema.T2 
   WHERE 
     LAST_UPDATE_DATE >= TO_DATE('01/01/2010','DD/MM/YYYY') 
     AND LAST_UPDATE_DATE < TO_DATE('01/01/2015','DD/MM/YYYY'))
T2 ON T1.ROW_ID = T2.ROW_ID

Here we have one join but in the execution plan, We have Nested loops and Hash Join as well.

Can anyone please guide me about how the flow works ?

enter image description here

Solution

  • It is due to "adaptive" plan. Oracle shows you all the candidates - in this case it can join using hash join OR nested loops. The output that you see does not display the indication which step is adaptive (optional) and which step is mandatory. As far as I understand you are using DBeaver, which is not aware of the "adaptive" feature of Oracle's execution plan, thus no indication.

    You may use the native way of displaying execution plans :

    SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

    For more details, please read great article of Tim Hall : https://oracle-base.com/articles/12c/adaptive-plans-12cr1