What is the role of the first "Nested Loop Join" in this plan

User_G33MB

Hi

I have a query as you can see here :

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 :

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 I understand from the given plan is that the join between all three tables are finished in the second nested loop which is operation number 2 , so what does the first Nested loop join do exactly?

PS: If here is not a suitable place for asking such questions please let me know and show me the correct room/place/whatever.

Thanks in advance