Oracle Scratchpad

April 27, 2020

Execution Plans

Filed under: Execution plans,Oracle — Jonathan Lewis @ 11:57 am BST Apr 27,2020

In a recent blog note I made the point that there is a very simple rule (“first child first”) for reading execution plans if the query (as written or after transformation by the optimizer) consists of a single “query block”. However, if you have a plan that is reporting multiple query blocks you have to be careful that you identify the boundaries of the individual query blocks and manage to link them together correctly.

In this note I’m going to work through an example that appeared on the Oracle Developer Forum a couple of years ago where someone produced a query that may have fooled them into misreading the execution plan. It’s a very good example of a production plan that is sufficiently complex to be interesting and make a couple of useful points but still produces a plan that is short enough to pick apart in just a couple of pages of text.

The initial question was about an oddity (actually a bug) in the optimizer’s cardinality estimate for one of the plan operations, but my answer to the question produced a follow-up question which said:

“Okay so it’s an error, was wondering why for one of the subqueries it’s using a hash join (7,8) and the other a nested loop.”

Here’s the query that produced this question. Before jumping straight to the plan it’s a good idea to take a close look at the query, take note of any optimizer strategies you might see, any features of the query that might make a big difference to the optimizer strategies, and any details that might mean the plan doesn’t do what you might expect.

Since the Oracle version can have a big impact on what the optimizer can do, I’ll point out that this was running on 12.1.0.2


select  /*+ gather_plan_statistics */ 
        * 
from 
        dwh_prod.ip_spells a
where 
        not exists (
                select  1
                from
                        dwh_prod.ip_episodes e
                inner join 
                        dwh_prod.ip_diagnosis d 
                on
                        d.episode_id = e.episode_id
                where
                        a.spell_id = e.spell_id
                and     (
                           substr(d.diagnosis_code,1,1) = 'C'
                        or substr(d.diagnosis_code,1,3) between 'D37' and 'D48'
                        or substr(d.diagnosis_code,1,1)  = 'V'
                        or d.diagnosis_code = 'Z511'
                )
        )
and     exists (
                select  1
                from
                        dwh_prod.ip_episodes e
                left join 
                        dwh_prod.ip_diagnosis d 
                on
                        d.episode_id = e.episode_id
                where
                        a.spell_id = e.spell_id
                and     (
                            e.episode_order = '1'
                        and substr(d.diagnosis_code,1,1) <> 'O'
                        and d.diagnosis_sequence = 1
                        and e.main_specialty_code not in ('501','560','610')
                        )
                )
and     substr(a.admission_method_code,1,1) = '2'
and     a.admission_dttm >= to_date('01-jan-2011', 'dd-mon-yyyy')
and     administrative_category_code = '01'

Point to note:

  • It’s a fairly simple query – one table, with two correlated subqueries.
  • The correlating predicate for the first (not exists) subquery is at line 15
  • The correlating predicate for the second (exists) subquery is at line 32
  • Both subqueries are joins between two tables, and the two tables are the same in both cases.
  • The first subquery is an “inner join” (line 10), the second subquery is a “left join” (line 27)
  • There’s an error in the code at line 36!
    • the predicate “d.diagnosis_sequence = 1” will eliminate any ip_episode (e) rows that have been preserved by the outer join
    • so the optimizer will automatically treat the outer join as an inner join
  • We might see either subquery running as a filter subquery
  • We might see either subquery unnested into an inline view – with, or without, “pushed predicate”
  • We might then see see complex view merging turn the “exists” subquery into a semi-join and/or the “not exists” into an anti-join

That last comment is something that makes it easy to jump to conclusions while reading the plan and head off in the wrong direction. So let’s take a careful look at the execution plan – which is an actual run-time plan reporting the rowsource execution statistics:


SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format=>'ALLSTATS LAST'));

Plan hash value: 2721384176
----------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
----------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |      1 |        |     50 |00:00:06.13 |     283K|  94413 |       |       |          |
|*  1 |  FILTER                               |                    |      1 |        |     50 |00:00:06.13 |     283K|  94413 |       |       |          |
|*  2 |   HASH JOIN ANTI                      |                    |      1 |    156 |     51 |00:00:05.70 |     282K|  94247 |   143M|  7865K|  143M (0)|
|   3 |    PARTITION RANGE ALL                |                    |      1 |  15592 |    425K|00:00:00.65 |   79553 |      0 |       |       |          |
|*  4 |     TABLE ACCESS FULL                 | IP_SPELLS          |     24 |  15592 |    425K|00:00:00.58 |   79553 |      0 |       |       |          |
|   5 |    VIEW                               | VW_SQ_1            |      1 |    530K|    464K|00:00:04.74 |     203K|  94247 |       |       |          |
|*  6 |     HASH JOIN                         |                    |      1 |    530K|    464K|00:00:04.65 |     203K|  94247 |    26M|  3954K|   34M (0)|
|*  7 |      TABLE ACCESS FULL                | IP_DIAGNOSIS       |      1 |    528K|    464K|00:00:03.12 |     109K|      0 |       |       |          |
|   8 |      TABLE ACCESS FULL                | IP_EPISODES        |      1 |   2491K|   2495K|00:00:00.44 |   94253 |  94247 |       |       |          |
|   9 |   NESTED LOOPS SEMI                   |                    |     51 |      1 |     50 |00:00:00.36 |     514 |    166 |       |       |          |
|* 10 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        |     51 |      1 |     51 |00:00:00.15 |     229 |     92 |       |       |          |
|* 11 |     INDEX RANGE SCAN                  | IDX02_IP_EPISODES  |     51 |      1 |     76 |00:00:00.13 |     153 |     51 |       |       |          |
|* 12 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       |     51 |   2688K|     50 |00:00:00.21 |     285 |     74 |       |       |          |
|* 13 |     INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS |     51 |      5 |    174 |00:00:00.21 |     153 |     74 |       |       |          |
----------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - access(A.SPELL_ID=ITEM_1)
   4 - filter((SUBSTR(A.ADMISSION_METHOD_CODE,1,1)='2' AND A.ADMISSION_DTTM>=TO_DATE(' 2011-01-01 00:00:00', 'syyyy-mm-dd hh24:mi:ss')
              AND ADMINISTRATIVE_CATEGORY_CODE='01'))
   6 - access(D.EPISODE_ID=E.EPISODE_ID)
   7 - filter((SUBSTR(D.DIAGNOSIS_CODE,1,1)='C' OR SUBSTR(D.DIAGNOSIS_CODE,1,1)='V' OR (SUBSTR(D.DIAGNOSIS_CODE,1,3)>='D37' AND
              SUBSTR(D.DIAGNOSIS_CODE,1,3)<='D48') OR D.DIAGNOSIS_CODE='Z511'))
  10 - filter((E.EPISODE_ORDER=1 AND E.MAIN_SPECIALTY_CODE<>'501' AND E.MAIN_SPECIALTY_CODE<>'560' AND
              E.MAIN_SPECIALTY_CODE<>'610'))
  11 - access(E.SPELL_ID=:B1)
  12 - filter((D.DIAGNOSIS_SEQUENCE=1 AND SUBSTR(D.DIAGNOSIS_CODE,1,1)<>'O'))
  13 - access(D.EPISODE_ID=E.EPISODE_ID)

Note
-----
   - dynamic statistics used: dynamic sampling (level=2)
   - this is an adaptive plan
   - 6 Sql Plan Directives used for this statement
   -

Take note, by the way, that we’ve been told that the plan was “adaptive” and we’ve used 6 SQL Plan Directives, which would (probably) have been telling the optimizer to use dynamic sampling of the data in various ways to get to the best possible plan.

Given that the query starts as three separate query blocks and uses two of the tables twice it would have been nice to see the Query Block / Object Alias information for this query as that would have made it just a little easier to connect the plan to the query.

At first sight we can spot that we’ve got a hash join anti at operation 2 and a nested loop semi at operation 9 and given my earlier comments about how the optimizer can treat subqueries it would be easy to jump to the conclusion that the anti represented the “not exists” subquery and the semi was the “exists” subquery and work onwards from there. However operation 1 is a filter, and when we check the Predicate Information for operation 1 it holds the slighltly cryptic content “filter (IS NOT NULL)” this is an example of how filter subqueries “go missing” from the predicate information when you pull the execution plan from memory. Operation 1 is a multi-child filter with operation 2 and operation 9 as its child operations; for each row it receives from operation 2 it calls operation 9. If we could see the full predicate information for operation 1 we would see that it was the existence subquery. The nested loop semi isn’t the result of Oracle executing the existence subquery as a sem-join, it’s a semi-join that has appeared for some other reason – that we will pursue in a little while.

Let’s examine operation 2 (and its descendents) in detail.  It’s a hash join so it’s first child will be used as the build table and it’s second child will be used as the probe table.  The first child is a full tablescan (operation 4)  reading every partition of a range partitioned (operation 3)  table – IP_SPELLS table, which is the one table in the main body of the query. The second child is a view called VW_SQ_1 – an example of an internally named view that can appear when Oracle unnests, but doesn’t merge, a subquery – it represents the body of the “not exists” subquery. Oracle has used a hash join with IP_DIAGNOSIS as the build table and IP_EPISODES as the probe table.

To make things a little easier to comprehend I’ve created tables and indexes that let me emulate this query and plan, using the alias format option to report the query block names and fully qualified aliases that were finally used. Then I’ve edited the output to put the query block names and aliases beside the operation they refer to (rather than leaving the in a block under the body of the plan) and I’ve inserted line-breaks to help highlight the separate query blocks . This is the result:

----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Query Block  / Alias                |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |                                     |
|*  1 |  FILTER                               |                    | SEL$2B969259                        |
|*  2 |   HASH JOIN ANTI                      |                    |                                     |
|   3 |    PARTITION RANGE ALL                |                    |                                     |
|*  4 |     TABLE ACCESS FULL                 | IP_SPELLS          | SEL$2B969259 / A@SEL$4              |

|   5 |    VIEW                               | VW_SQ_1            | SEL$8D33959D / VW_SQ_1@SEL$F49409E0 |
|*  6 |     HASH JOIN                         |                    | SEL$8D33959D                        |
|*  7 |      TABLE ACCESS FULL                | IP_DIAGNOSIS       | SEL$8D33959D / D@SEL$1              |
|   8 |      TABLE ACCESS FULL                | IP_EPISODES        | SEL$8D33959D / E@SEL$1              |

|   9 |   NESTED LOOPS SEMI                   |                    | SEL$2B0A33EA                        |
|* 10 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        | SEL$2B0A33EA / E@SEL$3              |
|* 11 |     INDEX RANGE SCAN                  | IDX02_IP_EPISODES  | SEL$2B0A33EA / E@SEL$3              |
|* 12 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       | SEL$2B0A33EA / D@SEL$3              |
|* 13 |     INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS | SEL$2B0A33EA / D@SEL$3              |
----------------------------------------------------------------------------------------------------------

As I’ve said before – if an execution plan is too complex to read in one go you can pick virtually any line of the plan and examine that line and its descendants in isolations as a way of getting started. Now I want to point out that if you can see the final query blocks this simply then any operations that starts a query block is a useful starting point for reading part of the plan because each query block has been optimized separately, and once you’ve understood how that block operates you can replace it (mentally) with a one line “here’s a rowsource”. So we might separate this plan into pieces as following:


----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Query Block  / Alias                |
----------------------------------------------------------------------------------------------------------
|   5 |    VIEW                               | VW_SQ_1            | SEL$8D33959D / VW_SQ_1@SEL$F49409E0 |
|*  6 |     HASH JOIN                         |                    | SEL$8D33959D                        |
|*  7 |      TABLE ACCESS FULL                | IP_DIAGNOSIS       | SEL$8D33959D / D@SEL$1              |
|   8 |      TABLE ACCESS FULL                | IP_EPISODES        | SEL$8D33959D / E@SEL$1              |
----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Query Block  / Alias                |
----------------------------------------------------------------------------------------------------------
|   9 |   NESTED LOOPS SEMI                   |                    | SEL$2B0A33EA                        |
|* 10 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        | SEL$2B0A33EA / E@SEL$3              |
|* 11 |     INDEX RANGE SCAN                  | IDX02_IP_EPISODES  | SEL$2B0A33EA / E@SEL$3              |
|* 12 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       | SEL$2B0A33EA / D@SEL$3              |
|* 13 |     INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS | SEL$2B0A33EA / D@SEL$3              |
----------------------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Query Block  / Alias                |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |                                     |
|*  1 |  FILTER                               |                    | SEL$2B969259                        |
|*  2 |   HASH JOIN ANTI                      |                    |                                     |
|   3 |    PARTITION RANGE ALL                |                    |                                     |
|*  4 |     TABLE ACCESS FULL                 | IP_SPELLS          | SEL$2B969259 / A@SEL$4              |
|   5 |    Rowsource "not exists"             | VW_SQ_1            | SEL$8D33959D / VW_SQ_1@SEL$F49409E0 |
|   9 |   Rowsource "exists"                  |                    | SEL$2B0A33EA                        |
----------------------------------------------------------------------------------------------------------

  • The first sub-plan is the unnested “not exists” subquery – which is a very simple plan to analyze.
  • The second sub-plan is the “exists” subquery – which is a very simple plan to analyze
  • The third sub-plan (with the substitutes for the other two query blocks) says: do a hash (anti) join between IP_SPELLS and the “not exists” rowsource and for each row that is produced create and check the “exists” rowsource to see if it produces a match, and forward the row if it does.

In this particular case the shape of the two subsidiary query blocks, and the pattern that we use to stitch the pieces together makes it look as if “first child first” applies across the whole plan. This, I think, is a key reason why people have had difficulty interpreting complex plans in the past. If every plan you’ve previously examined looks as if “first child first” always works you’re going to be baffled by a plan where the combination of query blocks seems to breaks that rule.

Putting the first pieces together, this is what happens as the FILTER operation calls its first child:

  • Operation 1 (FILTER) calls operation 2 (hash join anti) which calls operation 3 (partition range all) which calls operation 4 (table access full of IP_SPELLS)
  • Operation 4 is the first operation to generate a rowsource,which is passes up to operation 3.
  • Operation 3 immediately passes the rowsource on up to operation 2 (making operation 3 the second operation to generate a rowsource)
  • Operation 2 creates its build table then calls operation 5 (VIEW – its second rowsource) to supply the probe table.
  • Operation 5 calls operation 6 (hash join) which calls its first child, operation 7 (table scan full of IP_DIAGNOSIS)
  • Operation 7 is the third  operation to generate a rowsource, which it passes up to operation 6 to use as its build table.
  • Operation 6 creates its build table from the rowsource then calls operation 8 (table access full of IP_EPISODE)
  • Operation 8 is the fourth operation to generate a rowsource, which it passes up to operation 6 as its probe table
  • Operation 6 probes its build table as rows arrive from operation 8, and passes join results up to operation 5
  • Operation 6 is the fifth operation to generate a rowsource
  • Operation 5 immediately passes the rows up to its parent (operation 2), making it the sixth operaton to generate a rowsource.
  • Operation 2 is an “anti” hash join, so as each row arrives from operation 5 it probes its build table and if it finds a match it marks the row in the build table as one to be discarded. When operation 2 has exhausted the input from operation 5 it can pass any unmarked rows up to its parent (operation 1), making it the seventh operation to generate a rowsource.

So we’re now at operation 1 with an incoming rowsource which is all the rows in IP_SPELLS where a certain type of match in the IP_DIAGNOSIS and IP_EPISODE tables does not exist. This is how the order looks so far if we strip the plan to a minimum and edit in an “order” column:

----------------------------------------------------------------------------
| Id  | Operation                             | Name               | Order |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |       |
|*  1 |  FILTER                               |                    |       |
|*  2 |   HASH JOIN ANTI                      |                    |     7 |
|   3 |    PARTITION RANGE ALL                |                    |     2 |
|*  4 |     TABLE ACCESS FULL                 | IP_SPELLS          |     1 |
|   5 |    VIEW                               | VW_SQ_1            |     6 |
|*  6 |     HASH JOIN                         |                    |     5 |
|*  7 |      TABLE ACCESS FULL                | IP_DIAGNOSIS       |     3 |
|   8 |      TABLE ACCESS FULL                | IP_EPISODES        |     4 |
----------------------------------------------------------------------------

So now we come to the part of the plan that matches the “exists” subquery. For each row that operation 1 (FILTER) receives from its first child it calls operation 9 to run the “exists” subquery, which is a correlated subquery with a join between IP_EPISODES and IP_DIAGNOSIS. So why does Oracle use a nested loop join in the subquery rather than a hash join, and why. in particular, does that nested loop turn into a semi-join? What’s the difference between the “not exists” and the “exists”?

Imagine that for every row in IP_EPISODES there are 10 matching rows in IP_DIAGNOSIS.  When we check for “not exists” correlated to IP_EPISODES we will have to join to every single matching IP_DIAGNOSIS row because it might be the 10th which fails the test because it matches some extra filter predicate on IP_DIAGNOSIS. So the join is going to be a “high volume” join (hence a hash join is likely to become more appropriate).  On the other hand when we test for “exists” we may get lucky and only get as far as the first IP_DIAGNOSIS for an IP_EPISODES to prove existence – so it makes more sense to adopt a nested loop so that we can stop on the first match – and that, in fact, is why the nested loop in this case is a nested loop semi, it’s a code path specially designed to stop early.  (In fact, it’s also capable of using the same caching mechanism as scalar subquery caching so it can be even more efficient than just “stop on first match” – it can even stop before trying because “I’ve done that one before”).

So for each row that operation 1 (FILTER) receives from its first child it calls its second child (operation 9) to see if it will return a  row.

  • Operation 9 (nested loop semi) calls operation 10 (table access by rowid) which calls operation 11 (index range scan)
  • Operation 11 will be the eighth operation to return a rowsource, containing rowids, to operation 10
  • Operation 10 will be the ninth operation to return a rowsource, containing row data, to operation 9
  • For each row it receives operation 9 will call its second child (operation 12 (table access by rowid)) which calls operation 13 (index range scan)
  • Operation 13 will be the tenth operation to return a rowsource, containing rowids, to operation 12
  • Operation 12 will be the eleventh operation to return a rowsource, containing row data, to operation 9
  • Operation 9 will stop on the first row it receives and pass it up to opereation 1, making operation 9 the twelfth operation to return a rowsource
  • Operation 1, if it receives a row from operation 9, will pass the current row to the client interface, making it the thirteenth operation to return a rowsource.

So the final ordering is:

----------------------------------------------------------------------------
| Id  | Operation                             | Name               | Order |
----------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |       |
|*  1 |  FILTER                               |                    |    13 |
|*  2 |   HASH JOIN ANTI                      |                    |     7 |
|   3 |    PARTITION RANGE ALL                |                    |     2 |
|*  4 |     TABLE ACCESS FULL                 | IP_SPELLS          |     1 |
|   5 |    VIEW                               | VW_SQ_1            |     6 |
|*  6 |     HASH JOIN                         |                    |     5 |
|*  7 |      TABLE ACCESS FULL                | IP_DIAGNOSIS       |     3 |
|   8 |      TABLE ACCESS FULL                | IP_EPISODES        |     4 |
|   9 |   NESTED LOOPS SEMI                   |                    |    12 |
|* 10 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        |     9 |
|* 11 |     INDEX RANGE SCAN                  | IDX02_IP_EPISODES  |     8 |
|* 12 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       |    11 |
|* 13 |     INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS |    10 |
----------------------------------------------------------------------------

Numbers

As a closing point it’s worth picking up a couple of numbers.

One number that raised the original question is the E-Rows of 2688K at operation 12 – it’s obviously wrong give that it’s supposed to be the number of rows you get from the table after getting 5 rowids from the index range scan at operation 13. Odd little glitches like this seem to appear from time to time as new internal features are tweaked and odd bits of code aren’t made totally consistent. It’s a pity that it’s there, but if an odd cardinality glitch hasn’t been echoed into the cost (which could result in a change in execution plan) it’s not really important. Since I created a model of this query to get at the query block names I’ve also run the model against 19.3 and the error is still present in that version.

You will note that the E-rows at operation 2 is 156 – when you compare it with the 15,592 for operation 3 you can see that it’s a classic 1% guess (in this case for the effects of a “not exists” subquery).

It’s also significant that the E-Rows for operation 3 is only 15,592 when the A-Rows is 425K: the error is fairly large and if there’s a performance problem with this query this error might be the first point of investigation. Maybe we need a histogram on administrative_category_code, or need to do something to help Oracle with the (1% guess from) substr(admission_method_code,1,1) – but we may have some trouble because you can’t mix and match virtual columns and column groups in 12.1.0.2

 

6 Comments »

  1. […] couple of days ago I discussed an execution plan that displayed some variation in the way it handled subqueries and even threw in a little deception […]

    Pingback by Execution Plans | Oracle Scratchpad — April 29, 2020 @ 1:54 pm BST Apr 29,2020 | Reply

  2. Hi Jonathan,

    Thanks for the awesome post.It will be very helpful to us in reading some complex plans.
    I came across a recent post on internet posted by you about Hint Hacking from https://www.orafaq.com/aggregator/sources/300 Little confused, could you please help us in applying the same scenario to this complex sql.”like breaking the plan to pieces and stitching them in an order”.

    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    | Id  | Operation                                   | Name                           | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                            |                                |      1 |        |   574 (100)|      1 |00:00:00.02 |    3822 |       |       |         |
    |   1 |  SORT AGGREGATE                             |                                |      1 |      1 |            |      1 |00:00:00.02 |    3822 |       |       |         |
    |*  2 |   VIEW                                      |                                |      1 |      1 |   574   (2)|      0 |00:00:00.02 |    3822 |       |       |         |
    |*  3 |    COUNT STOPKEY                            |                                |      1 |        |            |      2 |00:00:00.02 |    3822 |       |       |         |
    |   4 |     VIEW                                    |                                |      1 |      1 |   574   (2)|      2 |00:00:00.02 |    3822 |       |       |         |
    |*  5 |      SORT ORDER BY STOPKEY                  |                                |      1 |      1 |   574   (2)|      2 |00:00:00.02 |    3822 |  2048 |  2048 | 2048  (0)|
    |*  6 |       FILTER                                |                                |      1 |        |            |    171 |00:00:00.02 |    3822 |       |       |         |
    |   7 |        NESTED LOOPS                         |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    3128 |       |       |         |
    |   8 |         NESTED LOOPS                        |                                |      1 |      1 |   568   (2)|    182 |00:00:00.02 |    2946 |       |       |         |
    |   9 |          NESTED LOOPS                       |                                |      1 |      1 |   567   (2)|    182 |00:00:00.02 |    2942 |       |       |         |
    |  10 |           NESTED LOOPS                      |                                |      1 |      1 |   566   (2)|    182 |00:00:00.02 |    2938 |       |       |         |
    |  11 |            NESTED LOOPS ANTI                |                                |      1 |      1 |   565   (2)|    182 |00:00:00.02 |    2752 |       |       |         |
    |  12 |             NESTED LOOPS ANTI               |                                |      1 |      1 |   562   (2)|    182 |00:00:00.02 |    2388 |       |       |         |
    |* 13 |              HASH JOIN                      |                                |      1 |      5 |   557   (2)|    182 |00:00:00.02 |    2022 |  1599K|  1599K| 1503K (0)|
    |  14 |               VIEW                          | index$_join$_008               |      1 |    127 |     2   (0)|    127 |00:00:00.01 |       8 |       |       |         |
    |* 15 |                HASH JOIN                    |                                |      1 |        |            |    127 |00:00:00.01 |       8 |  1368K|  1368K| 1522K (0)|
    |  16 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_CODE_UK             |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |
    |  17 |                 INDEX FAST FULL SCAN        | XXADM_LOVS_PK                  |      1 |    127 |     1   (0)|    127 |00:00:00.01 |       4 |       |       |         |
    |* 18 |               HASH JOIN                     |                                |      1 |    478 |   555   (2)|    182 |00:00:00.01 |    2014 |  1245K|  1245K| 1277K (0)|
    |  19 |                NESTED LOOPS                 |                                |      1 |    478 |   243   (2)|    209 |00:00:00.01 |     883 |       |       |         |
    |  20 |                 NESTED LOOPS                |                                |      1 |      1 |     2   (0)|      1 |00:00:00.01 |       4 |       |       |         |
    |  21 |                  TABLE ACCESS BY INDEX ROWID| XXADM_COLLEGE_MASTER_TBL       |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |       |       |         |
    |* 22 |                   INDEX UNIQUE SCAN         | XXADM_COLLEGES_PK              |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |       |       |         |
    |  23 |                  TABLE ACCESS BY INDEX ROWID| XXADM_LOV_MASTER_TBL           |      1 |      1 |     1   (0)|      1 |00:00:00.01 |       2 |       |       |         |
    |* 24 |                   INDEX UNIQUE SCAN         | XXADM_LOVS_PK                  |      1 |      1 |     0   (0)|      1 |00:00:00.01 |       1 |       |       |         |
    |* 25 |                 TABLE ACCESS FULL           | XXADM_APPLICANT_COURSPREFS_TBL |      1 |    478 |   241   (2)|    209 |00:00:00.01 |     879 |       |       |         |
    |* 26 |                TABLE ACCESS FULL            | XXADM_APPLICANT_DETAILS_TBL    |      1 |   6685 |   311   (2)|  10488 |00:00:00.01 |    1131 |       |       |         |
    |* 27 |              TABLE ACCESS BY INDEX ROWID    | XXADM_APPLICANT_COURSPREFS_TBL |    182 |   8881 |     1   (0)|      0 |00:00:00.01 |     366 |       |       |         |
    |* 28 |               INDEX UNIQUE SCAN             | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     0   (0)|    182 |00:00:00.01 |     184 |       |       |         |
    |  29 |             VIEW PUSHED PREDICATE           | VW_SQ_1                        |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |
    |  30 |              NESTED LOOPS                   |                                |    182 |      1 |     3   (0)|      0 |00:00:00.01 |     364 |       |       |         |
    |* 31 |               TABLE ACCESS BY INDEX ROWID   | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     2   (0)|      0 |00:00:00.01 |     364 |       |       |         |
    |* 32 |                INDEX UNIQUE SCAN            | XXADM_APPLCNT_PREF_ORDER_UK    |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     184 |       |       |         |
    |* 33 |               TABLE ACCESS BY INDEX ROWID   | XXADM_CATEGORY_MASTER_TBL      |      0 |      1 |     1   (0)|      0 |00:00:00.01 |       0 |       |       |         |
    |* 34 |                INDEX UNIQUE SCAN            | XXADM_CATEGORY_PK              |      0 |      1 |     0   (0)|      0 |00:00:00.01 |       0 |       |       |         |
    |  35 |            TABLE ACCESS BY INDEX ROWID      | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     186 |       |       |         |
    |* 36 |             INDEX UNIQUE SCAN               | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |       |       |         |
    |* 37 |           INDEX UNIQUE SCAN                 | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |       |       |         |
    |* 38 |          INDEX UNIQUE SCAN                  | XXADM_LOVS_PK                  |    182 |      1 |     0   (0)|    182 |00:00:00.01 |       4 |       |       |         |
    |  39 |         TABLE ACCESS BY INDEX ROWID         | XXADM_LOV_MASTER_TBL           |    182 |      1 |     1   (0)|    182 |00:00:00.01 |     182 |       |       |         |
    |* 40 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    182 |      1 |     3   (0)|     29 |00:00:00.01 |     507 |       |       |         |
    |* 41 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_PREFS_UK         |    182 |      5 |     2   (0)|   1450 |00:00:00.01 |     191 |       |       |         |
    |  42 |        TABLE ACCESS BY INDEX ROWID BATCHED  | XXADM_APPLICANT_COURSPREFS_TBL |    171 |      1 |     2   (0)|      0 |00:00:00.01 |     173 |       |       |         |
    |* 43 |         INDEX RANGE SCAN                    | XXADM_APPLCNT_APPLICANT_STATUS |    171 |      1 |     1   (0)|      0 |00:00:00.01 |     173 |       |       |         |
    |* 44 |        VIEW                                 | index$_join$_014               |      6 |      1 |     0   (0)|      0 |00:00:00.01 |      14 |       |       |         |
    |* 45 |         HASH JOIN                           |                                |      6 |        |            |      0 |00:00:00.01 |      14 |  1519K|  1519K|  666K (0)|
    |* 46 |          INDEX RANGE SCAN                   | XXADM_CATEGORY_PK              |      6 |      1 |     0   (0)|      6 |00:00:00.01 |       6 |       |       |         |
    |  47 |          INLIST ITERATOR                    |                                |      6 |        |            |     12 |00:00:00.01 |       8 |       |       |         |
    |* 48 |           INDEX UNIQUE SCAN                 | XXADM_CATEGORY_CODE_UK         |     12 |      1 |     0   (0)|     12 |00:00:00.01 |       8 |       |       |         |
    --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
    
    Query Block Name / Object Alias (identified by operation id):
    -------------------------------------------------------------
    
       1 - SEL$1
       2 - SEL$2        / from$_subquery$_001@SEL$1
       3 - SEL$2
       4 - SEL$7E0D484F / from$_subquery$_002@SEL$2
       5 - SEL$7E0D484F
      14 - SEL$082F290F / LMT_GENDER@SEL$3
      15 - SEL$082F290F
      16 - SEL$082F290F / indexjoin$_alias$_001@SEL$082F290F
      17 - SEL$082F290F / indexjoin$_alias$_002@SEL$082F290F
      21 - SEL$7E0D484F / CMT@SEL$3
      22 - SEL$7E0D484F / CMT@SEL$3
      23 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3
      24 - SEL$7E0D484F / LMT_EDUCATION_TYPE@SEL$3
      25 - SEL$7E0D484F / ACT@SEL$3
      26 - SEL$7E0D484F / ADT@SEL$3
      27 - SEL$7E0D484F / ACT3@SEL$7
      28 - SEL$7E0D484F / ACT3@SEL$7
      29 - SEL$A75BE177 / VW_SQ_1@SEL$67DC521B
      30 - SEL$A75BE177
      31 - SEL$A75BE177 / ACT1@SEL$8
      32 - SEL$A75BE177 / ACT1@SEL$8
      33 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9
      34 - SEL$A75BE177 / XXADM_CATEGORY_MASTER_TBL@SEL$9
      35 - SEL$7E0D484F / LMT_PASS@SEL$3
      36 - SEL$7E0D484F / LMT_PASS@SEL$3
      37 - SEL$7E0D484F / LMT_APPEARANCE@SEL$3
      38 - SEL$7E0D484F / LMT_RELIGION@SEL$3
      39 - SEL$7E0D484F / LMT_RELIGION@SEL$3
      40 - SEL$5        / ACT1@SEL$5
      41 - SEL$5        / ACT1@SEL$5
      42 - SEL$6        / ACT2@SEL$6
      43 - SEL$6        / ACT2@SEL$6
      44 - SEL$F665FE1B / XXADM_CATEGORY_MASTER_TBL@SEL$4
      45 - SEL$F665FE1B
      46 - SEL$F665FE1B / indexjoin$_alias$_001@SEL$F665FE1B
      48 - SEL$F665FE1B / indexjoin$_alias$_002@SEL$F665FE1B
    
    Outline Data
    -------------
      /*+
          BEGIN_OUTLINE_DATA
          IGNORE_OPTIM_EMBEDDED_HINTS
          OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
          DB_VERSION('12.1.0.2')
          OPT_PARAM('_optimizer_use_feedback' 'false')
          OPT_PARAM('_optimizer_dsdir_usage_control' 0)
          OPT_PARAM('_optimizer_adaptive_plans' 'false')
          OPT_PARAM('_optimizer_gather_feedback' 'false')
          ALL_ROWS
          OUTLINE_LEAF(@&quot;SEL$F665FE1B&quot;)
          OUTLINE_LEAF(@&quot;SEL$4&quot;)
          OUTLINE_LEAF(@&quot;SEL$5&quot;)
          OUTLINE_LEAF(@&quot;SEL$6&quot;)
          OUTLINE_LEAF(@&quot;SEL$A75BE177&quot;)
          PUSH_PRED(@&quot;SEL$7E0D484F&quot; &quot;VW_SQ_1&quot;@&quot;SEL$67DC521B&quot; 16 15)
          OUTLINE_LEAF(@&quot;SEL$082F290F&quot;)
          OUTLINE_LEAF(@&quot;SEL$7E0D484F&quot;)
          UNNEST(@&quot;SEL$9D10C90A&quot;)
          UNNEST(@&quot;SEL$7&quot;)
          OUTLINE_LEAF(@&quot;SEL$2&quot;)
          OUTLINE_LEAF(@&quot;SEL$1&quot;)
          OUTLINE(@&quot;SEL$180402DE&quot;)
          OUTLINE(@&quot;SEL$7E0D484F&quot;)
          UNNEST(@&quot;SEL$9D10C90A&quot;)
          UNNEST(@&quot;SEL$7&quot;)
          OUTLINE(@&quot;SEL$67DC521B&quot;)
          OUTLINE(@&quot;SEL$9D10C90A&quot;)
          UNNEST(@&quot;SEL$9&quot;)
          OUTLINE(@&quot;SEL$7&quot;)
          OUTLINE(@&quot;SEL$C04829E0&quot;)
          ELIMINATE_JOIN(@&quot;SEL$3&quot; &quot;CRMT&quot;@&quot;SEL$3&quot;)
          ELIMINATE_JOIN(@&quot;SEL$3&quot; &quot;MMT&quot;@&quot;SEL$3&quot;)
          OUTLINE(@&quot;SEL$8&quot;)
          OUTLINE(@&quot;SEL$9&quot;)
          OUTLINE(@&quot;SEL$3&quot;)
          NO_ACCESS(@&quot;SEL$1&quot; &quot;from$_subquery$_001&quot;@&quot;SEL$1&quot;)
          NO_ACCESS(@&quot;SEL$2&quot; &quot;from$_subquery$_002&quot;@&quot;SEL$2&quot;)
          INDEX_RS_ASC(@&quot;SEL$7E0D484F&quot; &quot;CMT&quot;@&quot;SEL$3&quot; (&quot;XXADM_COLLEGE_MASTER_TBL&quot;.&quot;COLLEGE_ID&quot;))
          INDEX_RS_ASC(@&quot;SEL$7E0D484F&quot; &quot;LMT_EDUCATION_TYPE&quot;@&quot;SEL$3&quot; (&quot;XXADM_LOV_MASTER_TBL&quot;.&quot;LOV_ID&quot;))
          FULL(@&quot;SEL$7E0D484F&quot; &quot;ACT&quot;@&quot;SEL$3&quot;)
          FULL(@&quot;SEL$7E0D484F&quot; &quot;ADT&quot;@&quot;SEL$3&quot;)
          INDEX_JOIN(@&quot;SEL$7E0D484F&quot; &quot;LMT_GENDER&quot;@&quot;SEL$3&quot; (&quot;XXADM_LOV_MASTER_TBL&quot;.&quot;LOV_CODE&quot;) (&quot;XXADM_LOV_MASTER_TBL&quot;.&quot;LOV_ID&quot;))
          INDEX_RS_ASC(@&quot;SEL$7E0D484F&quot; &quot;ACT3&quot;@&quot;SEL$7&quot; (&quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;APPLICANT_ID&quot; &quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;PREFERENCE_ORDER&quot;))
          NO_ACCESS(@&quot;SEL$7E0D484F&quot; &quot;VW_SQ_1&quot;@&quot;SEL$67DC521B&quot;)
          INDEX_RS_ASC(@&quot;SEL$7E0D484F&quot; &quot;LMT_PASS&quot;@&quot;SEL$3&quot; (&quot;XXADM_LOV_MASTER_TBL&quot;.&quot;LOV_ID&quot;))
          INDEX_RS_ASC(@&quot;SEL$7E0D484F&quot; &quot;LMT_APPEARANCE&quot;@&quot;SEL$3&quot; (&quot;XXADM_LOV_MASTER_TBL&quot;.&quot;LOV_ID&quot;))
          INDEX(@&quot;SEL$7E0D484F&quot; &quot;LMT_RELIGION&quot;@&quot;SEL$3&quot; (&quot;XXADM_LOV_MASTER_TBL&quot;.&quot;LOV_ID&quot;))
          LEADING(@&quot;SEL$7E0D484F&quot; &quot;CMT&quot;@&quot;SEL$3&quot; &quot;LMT_EDUCATION_TYPE&quot;@&quot;SEL$3&quot; &quot;ACT&quot;@&quot;SEL$3&quot; &quot;ADT&quot;@&quot;SEL$3&quot; &quot;LMT_GENDER&quot;@&quot;SEL$3&quot; &quot;ACT3&quot;@&quot;SEL$7&quot; &quot;VW_SQ_1&quot;@&quot;SEL$67DC521B&quot;
                  &quot;LMT_PASS&quot;@&quot;SEL$3&quot; &quot;LMT_APPEARANCE&quot;@&quot;SEL$3&quot; &quot;LMT_RELIGION&quot;@&quot;SEL$3&quot;)
          USE_NL(@&quot;SEL$7E0D484F&quot; &quot;LMT_EDUCATION_TYPE&quot;@&quot;SEL$3&quot;)
          USE_NL(@&quot;SEL$7E0D484F&quot; &quot;ACT&quot;@&quot;SEL$3&quot;)
          USE_HASH(@&quot;SEL$7E0D484F&quot; &quot;ADT&quot;@&quot;SEL$3&quot;)
          USE_HASH(@&quot;SEL$7E0D484F&quot; &quot;LMT_GENDER&quot;@&quot;SEL$3&quot;)
          USE_NL(@&quot;SEL$7E0D484F&quot; &quot;ACT3&quot;@&quot;SEL$7&quot;)
          USE_NL(@&quot;SEL$7E0D484F&quot; &quot;VW_SQ_1&quot;@&quot;SEL$67DC521B&quot;)
          USE_NL(@&quot;SEL$7E0D484F&quot; &quot;LMT_PASS&quot;@&quot;SEL$3&quot;)
          USE_NL(@&quot;SEL$7E0D484F&quot; &quot;LMT_APPEARANCE&quot;@&quot;SEL$3&quot;)
          USE_NL(@&quot;SEL$7E0D484F&quot; &quot;LMT_RELIGION&quot;@&quot;SEL$3&quot;)
          NLJ_BATCHING(@&quot;SEL$7E0D484F&quot; &quot;LMT_RELIGION&quot;@&quot;SEL$3&quot;)
          SWAP_JOIN_INPUTS(@&quot;SEL$7E0D484F&quot; &quot;LMT_GENDER&quot;@&quot;SEL$3&quot;)
          PQ_FILTER(@&quot;SEL$7E0D484F&quot; SERIAL)
          INDEX_RS_ASC(@&quot;SEL$A75BE177&quot; &quot;ACT1&quot;@&quot;SEL$8&quot; (&quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;APPLICANT_ID&quot; &quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;PREFERENCE_ORDER&quot;))
          INDEX_RS_ASC(@&quot;SEL$A75BE177&quot; &quot;XXADM_CATEGORY_MASTER_TBL&quot;@&quot;SEL$9&quot; (&quot;XXADM_CATEGORY_MASTER_TBL&quot;.&quot;CATEGORY_ID&quot;))
          LEADING(@&quot;SEL$A75BE177&quot; &quot;ACT1&quot;@&quot;SEL$8&quot; &quot;XXADM_CATEGORY_MASTER_TBL&quot;@&quot;SEL$9&quot;)
          USE_NL(@&quot;SEL$A75BE177&quot; &quot;XXADM_CATEGORY_MASTER_TBL&quot;@&quot;SEL$9&quot;)
          INDEX_RS_ASC(@&quot;SEL$6&quot; &quot;ACT2&quot;@&quot;SEL$6&quot; (&quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;APPLICANT_ID&quot; &quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;STATUS_FLAG&quot;))
          BATCH_TABLE_ACCESS_BY_ROWID(@&quot;SEL$6&quot; &quot;ACT2&quot;@&quot;SEL$6&quot;)
          INDEX_RS_ASC(@&quot;SEL$5&quot; &quot;ACT1&quot;@&quot;SEL$5&quot; (&quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;APPLICANT_ID&quot; &quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;COLLEGE_ID&quot;
                  &quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;COURSE_ID&quot; &quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;MEDIUM_ID&quot; &quot;XXADM_APPLICANT_COURSPREFS_TBL&quot;.&quot;HOSTEL_REQUIRED&quot;))
          BATCH_TABLE_ACCESS_BY_ROWID(@&quot;SEL$5&quot; &quot;ACT1&quot;@&quot;SEL$5&quot;)
          INDEX_JOIN(@&quot;SEL$4&quot; &quot;XXADM_CATEGORY_MASTER_TBL&quot;@&quot;SEL$4&quot; (&quot;XXADM_CATEGORY_MASTER_TBL&quot;.&quot;CATEGORY_ID&quot;) (&quot;XXADM_CATEGORY_MASTER_TBL&quot;.&quot;CATEGORY_CODE&quot;))
          END_OUTLINE_DATA
      */
    

    Thanks,
    John

    Comment by John — May 2, 2020 @ 3:07 am BST May 2,2020 | Reply

  3. Hi Jonathan,

    All your posts are very keen to go through and useful to fix real time scenarios.Not sure why,but this SQL has drawn my attention to go through.I tried to break down this SQL into pieces and combine them together but could not. Thanks for letting us know that you will be writing some post on this SQL.

    Thanks and Regards,
    John

    Comment by John — May 3, 2020 @ 12:33 pm BST May 3,2020 | Reply

    • John,
      The statment and its plan make a fairly nice example to use as an educational example because is shows a number of different ways in which Oracle has handled subqueries, including:

      a) filter subqueries that have not been unnested
      b) a filter (& scalar) subquery in a CASE statement that has not been unnested, and has been camouflaged by another transformation
      c) a filter subquery that has been unnested by not been merged
      d) a filter subquery that has been unnested and merged

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — May 3, 2020 @ 3:40 pm BST May 3,2020 | Reply

  4. […] a comment to a recent post on reading a non-trivial execution someone asked me to repeat the exercise using a plan I had […]

    Pingback by Execution Plans | Oracle Scratchpad — May 5, 2020 @ 12:36 pm BST May 5,2020 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Powered by WordPress.com.