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 :
I have some question regarding the plan :
-
I expected the
operation-10
to be under theoperation-11
(op-10 be the child of op-11) because the'IDX_CUSTNUM'
index is for'MI_DIMCUSTOMER'
table ! Take a look atop-5 and op-6
for example . Orop-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 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:
IDX1_SEGMENT
is scanned to retrieveROWID
s matching the segment.MI_SEGMENT_CUSTOMER_RELATION
are read byROWID
to filter those that match theEFFECTIVE_DATE
condition and getCUSTOMER_NUM
.CUSTOMER_NUM
values.IDX1_F
is scanned to find entries matchingCUSTOMER_NUM
from Op. 5, and the correspondingROWID
s are retrieved.MI_FACTCUSTOMER
are read byROWID
to filter those that match theEFFECTIVE_DATE
condition and getCUSTOMER_NUM
.CUSTOMER_NUM
values from Op. 8.IDX_CUSTOMER
to fetchROWID
s matching the customer number.ROWID
s from Op. 10.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.