Discussions
Categories
- 17.9K All Categories
- 3.4K Industry Applications
- 3.3K Intelligent Advisor
- 62 Insurance
- 536K On-Premises Infrastructure
- 138.2K Analytics Software
- 38.6K Application Development Software
- 5.7K Cloud Platform
- 109.4K Database Software
- 17.5K Enterprise Manager
- 8.8K Hardware
- 71.1K Infrastructure Software
- 105.2K Integration
- 41.6K Security Software
What is the role of the first "Nested Loop Join" in this plan

User_G33MB
Member Posts: 1 Green Ribbon
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 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 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