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