Skip to Main Content

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question, krishna.

Asked: November 28, 2018 - 6:05 pm UTC

Last updated: November 30, 2018 - 6:13 am UTC

Version: 12c

Viewed 1000+ times

You Asked

Hi Team ,

can you help me to analyse the below execution plan : 

--------------------------------------------------------------------------------------------------------
| Id  | Operation                      | Name                  | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |                       |       |       |  2437 (100)|          |
|   1 |  NESTED LOOPS                  |                       |  2803 |   276K|  2437   (1)| 00:00:04 |
|   2 |   NESTED LOOPS                 |                       | 19745 |   276K|  2437   (1)| 00:00:04 |
|   3 |    SORT UNIQUE                 |                       |   109 |   872 |     2   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID| TABLE_A                     |   109 |   872 |     2   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN          | index_A               |   109 |       |     1   (0)| 00:00:01 |
|*  6 |    INDEX RANGE SCAN            | INDEX_B               |   359 |       |     1   (0)| 00:00:01 |
|*  7 |   TABLE ACCESS BY INDEX ROWID  | TABLE_B                     |    26 |  2418 |    44   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------


1.want to know the flow of execution
2.Sort unique is for INDEX_A or INDEX_B

Regards,
Krishna

and Connor said...

The order can be thought of as this:

- we scanned index A to get rowids (id 5)
- we used the rowids to access some rows in table A (id 4)
- we sorted those results to get a unique list (id 3)
- we used that data to lookup index B (id 6) and get rowids
- we used the rowids to lookup table B (id 7)

Rating

  (1 rating)

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

Comments

A reader, November 29, 2018 - 1:42 am UTC

Hi,

Thanks for prompt response

Nested loop on step 2 is for sorting indexA? And nested loop for 1 is joining of rows from index a and b?
Connor McDonald
November 30, 2018 - 6:13 am UTC

A nested loop will look like:

NESTED LOOP
- operation 1
- operation 2

Each row that comes out of operation 1, provides a piece of information that required to commence operation 2.

Operation 1 and 2 may in themselves be more complex steps.
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 latest video and Chris's latest video from their Youtube channels. And of course, keep up to date with AskTOM via the official twitter account.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library