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
  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

  • 1
    You probably should post the plan as properly formatted text, not as an image.
    – mustaccio
    Jan 9, 2022 at 19:33
  • @mustaccio . Thanks for the comment .Just give me some time and I'm gonna do that .
    – Pantea
    Jan 9, 2022 at 19:54

1 Answer 1


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.

  • Thanks for the explanation . Still have not got the answer to the second question which is why do we see 3 Nested Loops despite having 2 Inner joins in the query? 3 nested loops in the plan is a bit confusing for me
    – Pantea
    Jan 21, 2022 at 13:42
  • Still can not understand the role of first Nested loop! What does it do exactly ?? In the second Nested loop which is operation number 2 , all the joins between three tables are finished and the rows satisfying the condition of the joins are survived ! what does the first nested loop join do ?
    – Pantea
    Jan 21, 2022 at 13:47

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.