Oracle – Nested Loop Join in Execution Plan Despite Using Inner Joins

execution-planoracleoracle-11g-r2plsqlquery-performance

I have a query as you can see below :

with cte as
 (select customer_num
    from vmi_segment_customer_relation
   where effective_date = to_date('12/30/2021', 'mm/dd/yyyy')
     and segment_id = 10000000592
     )
select 
 t.customer_num, 
 cust_first_name, 
 cust_last_name, 
 cust_type_desc
  from vmi_factcustomer t
  join cte f
    on t.customer_num = f.customer_num
   and t.effective_date = to_date('12/30/2021', 'mm/dd/yyyy')

  join vmi_dimcustomer d
    on t.customer_num = d.customer_num;

As you can see, there are three tables in this query

1)vmi_segment_customer_relation , Index: "IDX1_SEGMENT" on "segment_id" column.

2)vmi_factcustomer , Index: "IDX1_F" on "customer_num" column.

3)vmi_dimcustomer , Index: "IDX_CUSTNUM" on "customer_num" column.

All table's statistics are up to date and there is no stale statistics. I got the real execution plan for this query using this hint /*+gather_plan_statistics*/ as you can see here is the plan :

enter image description here

I have some question regarding the plan :

  1. I expected the operation-10 to be under the operation-11 (op-10 be the child of op-11) because the 'IDX_CUSTNUM' index is for 'MI_DIMCUSTOMER' table ! Take a look at op-5 and op-6 for example . Or op-8 and op-9 , I expected to wee op-10 and op-11 exactly like the two and have no idea why it is not!

  2. Another question is that , there are two joins in the query , so why we see three Nested loop joins in the plan? What is the role for each nested loops??

Thanks in advance

Best Answer

You seem to be assigning too much meaning to the operation ID. It is there simply to uniquely identify each operation and has nothing to do with the order of operation execution.

You need to read the plan "inside out", from the most nested operation to the less nested one. With that in mind, let's see what is happening:

  • Op. 6. IDX1_SEGMENT is scanned to retrieve ROWIDs matching the segment.
  • Op. 5. The rows from MI_SEGMENT_CUSTOMER_RELATION are read by ROWID to filter those that match the EFFECTIVE_DATE condition and get CUSTOMER_NUM.
  • Op. 3. Loop over the retrieved CUSTOMER_NUM values.
  • Op. 9. In the loop IDX1_F is scanned to find entries matching CUSTOMER_NUM from Op. 5, and the corresponding ROWIDs are retrieved.
  • Op. 8. The rows from MI_FACTCUSTOMER are read by ROWID to filter those that match the EFFECTIVE_DATE condition and get CUSTOMER_NUM.
  • Op. 2. Loop over the CUSTOMER_NUM values from Op. 8.
  • Op. 10. In the loop access IDX_CUSTOMER to fetch ROWIDs matching the customer number.
  • Op. 1. Loop over ROWIDs from Op. 10.
  • Op. 11. In the loop access MI_DIMCUSTOMER to fetch the required columns.

Obviously, those loops are executed simultaneously: as soon as the new value from Op. 5 is available, the next iteration of Ops. 9 and 8 can proceed, and as soon as that is complete, the next iteration of Ops. 10 and 11 can fetch the required data.

Given the small number of rows and the presence of useful indexes the choice of the nested loop join seems appropriate.

Related Question