Thanks for the question, Pantea.
Asked: January 24, 2022 - 6:42 pm UTC
Last updated: January 25, 2022 - 7:17 am UTC
Version: 11g,r2
Viewed 1000+ times
You Asked
Hi.
My question is about "execution plan". I have a query as you can see below :
select
t2.n1, t1.n2
from
t2,t1
where
t2.n2 = 45
and t2.n1 = t1.n1
;There’s an index to support the join from t2 to t1 and I've Used "dbms_xplan.display_cursor" to get the real execution plan for this query . what I've got is this plan :
-------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
-------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 225 |00:00:00.01 | 3048 |
| 1 | NESTED LOOPS | | 1 | 225 | 225 |00:00:00.01 | 3048 |
| 2 | NESTED LOOPS | | 1 | 225 | 225 |00:00:00.01 | 3038 |
|* 3 | TABLE ACCESS BY INDEX ROWID| T2 | 1 | 15 | 15 |00:00:00.01 | 3013 |
| 4 | INDEX FULL SCAN | T2_I1 | 1 | 3000 | 3000 |00:00:00.01 | 13 |
|* 5 | INDEX RANGE SCAN | T1_I1 | 15 | 15 | 225 |00:00:00.01 | 25 |
| 6 | TABLE ACCESS BY INDEX ROWID | T1 | 225 | 15 | 225 |00:00:00.01 | 10 |
-------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter("T2"."N2"=45)
5 - access("T2"."N1"="T1"."N1")My question is that despite having only one join in the query , why do we see two "Nested Loops" in the plan ?
I've searched about this and the only thing I found was that it is called "decoupling of index access and table access" , but I couldn't find any detail information regarding this . I was wondering if you could explain a little bit more about that.
Thanks in advance
and Connor said...
Its an optimization that came in around 11g I think. In a "traditional" nested loop you would (in english terms)
-- get a row from T2
--- do an index read on T1, apply any potential filters if possible
--- do a table read on T1, apply any additional filters
Each read to the table T1 row might be anywhere on disk which means a stack of random I/O.
However, I can also do this:
-- get a row from T2
--- do an index read on T1, apply any potential filters if possible
and do just that part repeatedly , giving me some T2 rows and just key fields and the rowid from T1
This has some benefits
a) we're joining to a smaller structure (the index) - so potentially quicker to complete
b) Now that I have a batch of "incomplete" rows - but now because its a batch, there are opportunities for further optimizations. For example, I could sort the rowids by (say) file/block in order to then more efficiently use them to now go read the rest of the data from T1.
The second "nested loop" is in effect a join from the result of above to T1 in a more optimal fashion to "complete" the rest of the data. It almost not really a join, more a lookup with a set of rowids
Is this answer out of date? If it is, please let us know via a Comment