Skip to Main Content
  • Questions
  • What does decoupling of index and table access mean in an execution plan?

Breadcrumb

Question and Answer

Connor McDonald

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



More to Explore

Performance

Get all the information about database performance in the Database Performance guide.