Skip to Main Content
  • Questions

  • What does decoupling of index and table access mean in an execution plan?

Breadcrumb

Connor and Chris will both be at AI World from October 12 to October 17 , the premier Oracle conference of 2025. If you're in Vegas, please come say Hi or pop into our sessions

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




Is this answer out of date? If it is, please let us know via a Comment
more

Connor and Chris don't just spend all day on AskTOM. You can also catch regular content via Connor's blog and Chris's blog. Or if video is more your thing, check out Connor's channel and Chris's channel from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

Performance

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