Oakies Blog Aggregator

In previous articles on reading execution plans I’ve made the point that the optimizer is very “keen” to transform complex queries into queries consisting of a single query block and that there’s a simple “First Child First (FCF)” rule for reading the plan for a single query block. I’ve then pointed out that when the optimizer can’t transform your query into a single query block you can still apply FCF to each “final” query block (outline_leaf) in turn, but you then have to work out how Oracle is connecting those query blocks and FCF is not guaranteed to apply between query blocks.

In this note I want to follow-up an earlier comment that “The FILTER operation covers a multitude of sins.” because the filter operation (and variations thereof) often goes hand in hand with multiple query blocks and often (especially in recent versions of Oracle) needs a little care when you’re looking at a larger plan.

First though – a silly little question:

How many rows will be returned by the query “select * from tableX where 1 = 2”?

The answer is obviously “none”. But here’s the execution plan for a query of that form (cut and pasted from an SQL*Plus session in 19.3):

SQL> create table t1 as select * from all_objects where rownum <= 1000 -- > hint to avoid wordpress format issue
 2   /

Table created.

SQL> set autotrace traceonly explain
SQL> select * from t1 where 1 = 2;

Execution Plan
----------------------------------------------------------
Plan hash value: 3332582666

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |   116 |     0   (0)|          |
|*  1 |  FILTER            |      |       |       |            |          |
|   2 |   TABLE ACCESS FULL| T1   |  1000 |   113K|     4   (0)| 00:00:01 |
---------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(NULL IS NOT NULL)

Although it’s clearly impossible for any rows to be returned Oracle (apparently) wants to do a full tablescan. Of course it doesn’t actually do that tablescan; if you check the Predicate Information there’s a filter predicate at Operation 1 that tests for “null is not null”^[1], which is never true, and one of the refinements on the basic “first child first” is that a child operation is called only if the parent thinks it is needed. Consider, for example, the following query with its execution plan (again cut-n-pasted from SQL*Plus 19.3.0.0 with the same table t1):

SQL> set serveroutput off
SQL> alter session set statistics_level = all;

Session altered.

SQL> select t1.*
  2  from   t1, t1 t2
  3  where  t1.object_id < 0 -- > comment to avoid wordpress format issue
  4  and    t2.object_id = t1.object_id 
  5  /

no rows selected 

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  86ytbkc1fpbzt, child number 0
-------------------------------------
select t1.* from   t1, t1 t2 where  t1.object_id < 0 and
t2.object_id = t1.object_id

Plan hash value: 1734879205

-------------------------------------------------------------------------------------------------------------------------
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |      1 |        |      0 |00:00:00.01 |      22 |     18 |       |       |          |
|*  1 |  HASH JOIN         |      |      1 |      1 |      0 |00:00:00.01 |      22 |     18 |   799K|   799K|  199K (0)|
|*  2 |   TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |      19 |     18 |       |       |          |
|*  3 |   TABLE ACCESS FULL| T1   |      0 |      1 |      0 |00:00:00.01 |       0 |      0 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - access("T2"."OBJECT_ID"="T1"."OBJECT_ID")
   2 - filter("T1"."OBJECT_ID"<0)
   3 - filter("T2"."OBJECT_ID"<0)

If you look at the Starts column for operation 3 you’ll see that the second tablescan doesn’t run. This makes sense since the A-Rows column of operation 2 reports zero rows, which means the hash join at operation 1 has no data for its build table, so there’s no point  in it calling operation 3 to search for probe  data that has nothing to match. (This optimization doesn’t necessarily appear for parallel hash joins).

You probably won’t see many cases of people writing code with literal predicates quite like “1 = 2”, of course, but you might see predicates like “:bindvar1 = ‘M'” fairly often, either because the client code really does contain the predicate or because the optimizer has produced it through some transformation (such as concatentation or nvl_or_expansion or transitive closure).

^[1] In older versions of Oracle a predicate that was always false (i.e. a contradiction) would have been transformed into the predicate “1=0”

Constant Subqueries

Another example of a less common appearance of the FILTER operation comes from a requirement like: “if there are more than 10 orders outstanding show them to me”. Using the t1 table above, this could be modelled with a query like:

SQL> set serveroutput off
SQL> alter session set statistics_level = all;

Session altered.

SQL> select * from t1 
  2  where 10 <= (select count(*) from t1 where object_id < 0) -- > comment to avoid wordpress format issue
  3  /

no rows selected

SQL> select * from table(dbms_xplan.display_cursor(null,null,'allstats last alias'));

PLAN_TABLE_OUTPUT
-------------------------------------
SQL_ID  2pha6dc0b9zzq, child number 1
-------------------------------------
select * from t1 where 10 <= (select count(*) from t1 where object_id < 0) -- > comment to avoid wordpress format issue

Plan hash value: 2626881942

--------------------------------------------------------------------------------------
| Id  | Operation           | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT    |      |      1 |        |      0 |00:00:00.01 |      19 |
|*  1 |  FILTER             |      |      1 |        |      0 |00:00:00.01 |      19 |
|   2 |   TABLE ACCESS FULL | T1   |      0 |   1000 |      0 |00:00:00.01 |       0 |
|   3 |   SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      19 |
|*  4 |    TABLE ACCESS FULL| T1   |      1 |      1 |      0 |00:00:00.01 |      19 |
--------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$1
   2 - SEL$1 / T1@SEL$1
   3 - SEL$2
   4 - SEL$2 / T1@SEL$2

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(>=10)
   4 - filter("OBJECT_ID"<0)

If you go at this plan in too much of a rush you might think it is a single query block and apply FCF to produce the description:
“For each row in the tablescan of t1 at operation 2 the FILTER operation at operation 1 calls operation 3 to do a full tablescan (operation 4) of the second copy of t1 – scalar subquery caching means we only do that tablescan once and then cache the result.”

But the query clearly starts out with two (unnamed) query blocks, and the Query Block Name / Object Alias information shows us we still have two query blocks, and the Starts column tells us that operation 2 didn’t run at all. This is a case where we have to think carefully about how to combine multiple query blocks after interpreting the sub-plan for each query block individually.

In this example, which I call the “constant subquery”, Oracle can evaluate the subquery first to decide whether or not to run the main query, and the visual impact of this is that the second (last) child of the FILTER runs before the first child. This is not breaking FCF – it’s just one of the patterns you have to recognise as Oracle combines multiple query blocks in a single plan.

Correlated Filter Subqueries

Moving on to one of the most common types of filter subquery – a simple correlated subquery – there are four patterns to watch out for, and a fifth pattern that isn’t a filter subquery that might fool you if you get too confident of handling filter subqueries. I’ll be using hints fairly aggressively to force the plans I want to see, but all the plans that I’ll show in this section could appear as a consequence of basic costing.

We start with an order_lines table and a products table, and have a requirement to report any order lines for a given product class where the number of items ordered is 6.

select  /*+ 
                qb_name(main) 
        */
        orl.*
from    order_lines     orl
where
        orl.quantity = 6
and     orl.id_product in (
                select  /*+ 
                                qb_name(class) 
                        */
                        prd.id
                from    products prd
                where   prd.class = 'Group25'
        )
;

In the four plans I’m going to show you I’ve added the /*+ no_unnest */ hint to the subquery (though I’ve not shown it in the text above) to make sure that the optimizer doesn’t transform the subquery into a join.

In the first plan I don’t have any indexes on the order_lines table that could help eliminate data early, I’ve also added the hint /*+ no_push_subq */ to the subquery so that the optimizer doesn’t try to move the subquery to the earlies possible point in the plan. The resulting plan looks like the “traditional” plan for a filter subquery – reporting a FILTER operation that (notionally) executes the subquery for each row returned by a tablescan.

NO_UNNEST, NO_PUSH_SUBQ
-----------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |       |          |
|*  1 |  FILTER                      |             |       |          |
|*  2 |   TABLE ACCESS FULL          | ORDER_LINES |  5556 | 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 | 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 | 00:00:01 |
-----------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN
   2 - MAIN  / ORL@MAIN
   3 - CLASS / PRD@CLASS
   4 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - filter("ORL"."QUANTITY"=6)
   3 - filter("PRD"."CLASS"='Group25')
   4 - access("PRD"."ID"=:B1)

   1 - filter( EXISTS (SELECT /*+ NO_PUSH_SUBQ NO_UNNEST QB_NAME ("CLASS") */ 0 
                       FROM "PRODUCTS" "PRD" 
                       WHERE "PRD"."ID"=:B1 AND "PRD"."CLASS"='Group25'))

As you can see, I’ve reported the Query Block / Object Alias information and the two original query blocks are still clearly visible in this plan. Since I’ve pulled this plan from memory you’ll also note in the Predicate Information that the FILTER operation has “lost” the text of the filter() predicate.  This is why I’ve added at the end of the output the filter predicate reported by using explain plan with dbms_xplan.display().

For the next run I’m going to add an index on the quantity column of the order_lines table, and I’m going to tell the optimizer to run the subquery at the earliest possible moment (using the push_subq hint). This can produce two different plans – depending on whether or not the optimizer thinks it would be efficient to use the new index.

First, when the optimizer ignores the index:

NO_UNNEST, PUSH_SUBQ, order_lines index ignored
-----------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Time     |
-----------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |   282 | 00:00:01 |
|*  1 |  TABLE ACCESS FULL           | ORDER_LINES |   282 | 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| PRODUCTS    |     1 | 00:00:01 |
|*  3 |    INDEX UNIQUE SCAN         | PRD_PK      |     1 | 00:00:01 |
-----------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN  / ORL@MAIN
   2 - CLASS / PRD@CLASS
   3 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("ORL"."QUANTITY"=6 AND  IS NOT NULL)
   2 - filter("PRD"."CLASS"='Group25')
   3 - access("PRD"."ID"=:B1)


   1 - filter("ORL"."QUANTITY"=6 AND  EXISTS (SELECT /*+ PUSH_SUBQ
              NO_UNNEST QB_NAME ("CLASS") */ 0 FROM "PRODUCTS" "PRD" WHERE
              "PRD"."ID"=:B1 AND "PRD"."CLASS"='Group25'))

In the above you can see that Operation 2 appears to be the first child of operation 1 and if we applied FCF we would read this as “first use the primary key on products to pick up a single product and use it during a full tablescan of order_lines“. Fortunately we can see the separate query blocks and so we ignore FCF. The filter() predicate for operation 1 (again revealed by the explain plan version) tells us that we have a filter subquery so “for each row we read in the tablescan we check if the quantity is 6 and if so we execute the subquery to see if the product is in class 25″. You’ll notice the :B1 in the filter predicate – this is the correlation variable where Oracle passes the order_lines.id_product to the subquery.

Now when the optimizer uses the index (which I had to hint in this case):

NO_UNNEST, PUSH_SUBQ, order_lines index used (quantity)
------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |       |          |
|*  1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_LINES |   122 | 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORL_QTY     |  5556 | 00:00:01 |
|*  3 |   TABLE ACCESS BY INDEX ROWID       | PRODUCTS    |     1 | 00:00:01 |
|*  4 |    INDEX UNIQUE SCAN                | PRD_PK      |     1 | 00:00:01 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN  / ORL@MAIN
   2 - MAIN  / ORL@MAIN
   3 - CLASS / PRD@CLASS
   4 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter( IS NOT NULL)
   2 - access("ORL"."QUANTITY"=6)
   3 - filter("PRD"."CLASS"='Group25')
   4 - access("PRD"."ID"=:B1)


   1 - filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST QB_NAME ("CLASS")
              */ 0 FROM "PRODUCTS" "PRD" WHERE "PRD"."ID"=:B1 AND
              "PRD"."CLASS"='Group25'))

In this case operation 1 is a “table access by rowid” that appears to have two child operations! Again, though, the query block information tells us that there is a separate query block starting at operation 3. So FCF applies only to operations 1 and 2, and we have to decide how to bring in the query block described by operations 3 and 4.

Checking the predicate information we can see, once again, that there is a “filter() gone empty” at operation 1. And the filter() predicate from explain plan tells us that this corresponds to executing a subquery against the products table. In this case we have simply used the index on (order_lines.quantity) to access only the rows with the correct quantity, and then executed the products subquery for each of those rows.

Finally I’ve created an index on order_lines(quantity, id_product), and forced the optimizer to use it with the following effect:

NO_UNNEST, PUSH_SUBQ, order_lines index used (quantity, id_product)
------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Time     |
------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |       |          |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_LINES |   122 | 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORL_QTY_PRD |   122 | 00:00:01 |
|*  3 |    TABLE ACCESS BY INDEX ROWID      | PRODUCTS    |     1 | 00:00:01 |
|*  4 |     INDEX UNIQUE SCAN               | PRD_PK      |     1 | 00:00:01 |
------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - MAIN  / ORL@MAIN
   2 - MAIN  / ORL@MAIN
   3 - CLASS / PRD@CLASS
   4 - CLASS / PRD@CLASS

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORL"."QUANTITY"=6)
       filter( IS NOT NULL)
   3 - filter("PRD"."CLASS"='Group25')
   4 - access("PRD"."ID"=:B1)


   2 - access("ORL"."QUANTITY"=6)
       filter( EXISTS (SELECT /*+ PUSH_SUBQ NO_UNNEST QB_NAME ("CLASS")
              */ 0 FROM "PRODUCTS" "PRD" WHERE "PRD"."ID"=:B1 AND
              "PRD"."CLASS"='Group25'))

It would be very easy to read this plan using FCF from top to bottom – and then have to wonder how Oracle managed to find a single product to drive the query. But the query block information rescues us from this error, and we realise that there’s no predicate associatecd with operation 1 so we need to see a way that we can connect the index range scan at operation 2 with the subquery at operations 3 and 4.

Checking the predicate section (and the explan plan predicate) at operation 2 we can see that we execute the subquery as we are running the index range scan of the order_lines index and before we use any rowids to visit the table. So Oracle picks up an index entry (which includes a product id), executes the subquery for that entry’s product id, and only visits the order_lines table if the subquery says the product is in class 25.

Summary Warning

If the optimizer needs to execute a subquery as a filter subquery there are 4 basic patterns you might see in the execution plan. One of them includes an explicit, standalone, FILTER operation; the other three “hide” the filter operation and it appears only as a filter() predicate.

In two of the “hidden filter” cases the shape of the plan is distorted in a way that looks a little strange until you realise that there are two query blocks involved and FCF doesn’t apply across the query blocks; in the third case it would be very easy to read down the plan thinking that FCF was appropriate because the shape of the plan looks perfectly reasonable.

Footnote (The “driving” subquery)

It’s worth seeing one more query involving the order_lines table and giving us a fifth execution plan that highlights the ease with which subqueries can cause (temporary) confusion.

select  /*+ qb_name(main) */
        orl.*
from    order_lines orl
where   orl.id_ord = (
                select
                        /*+ qb_name(max) */ 
                        max(ord.id) 
                from    orders ord
        )
/

---------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |             |     5 |    90 |     9   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID BATCHED| ORDER_LINES |     5 |    90 |     7   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN                  | ORL_PK      |     5 |       |     2   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE                   |             |     1 |     4 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)       | ORD_PK      |     1 |     4 |     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------

   1 - MAIN / ORL@MAIN
   2 - MAIN / ORL@MAIN
   3 - MAX
   4 - MAX  / ORD@MAX

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("ORL"."ID_ORD"=)

   2 - access("ORL"."ID_ORD"= (SELECT /*+ QB_NAME ("MAX") */ MAX("ORD"."ID") FROM "ORDERS"
              "ORD"))

As in earlier examples in this note I’ve reported the Predicate Information from the in-memory plan then followed this with the predicate section I got from using explain plan.

The basic shape of the plan suggests a simple FCF, and in this case if you follow that route you’ll get the right interpretation of how Oracle executes the query – but you’ll have got it right for the wrong reason.

The query block information tells you that operations 3 and 4 come from a separate query block so should be examined in isolation from operations 1 and 2, which form the main query block. To combine the query blocks we then note that operation 2 (the index range scan) uses the subquery as an access predicate, not a filter predicate, so the subquery has to execute first to supply a value for the main query to use as a driving value for the index range scan.

When you have to handle subqueries in execution plans make sure you check whether they operate as filter subqueries or access subqueries before you try to interpret how the interact with the rest of the plan.

You’ve probably heard that Oracle have made some training and certifications free in recent times (here). We are approaching the end of that period now. Only about 15 more days to go.

Initially I thought I might try and do all the certifications, but other factors got in the way, so I just decided to do one. You can probably guess which one by the title of this post.

• Uncategorized

Preface PL/SQL is one of the top twenty programming languages in the world. While I’m no Matthew Symonds, and this is no Softwar, the history of PL/SQL deserves a helluva lot more than, “Oracle 7 had an advanced architecture and, like Sybase, was fully programmable via its elegant new PL/SQL programming language.” That’s why, during the process working on […]

The post A (Not So) Brief But (Very) Accurate History of PL/SQL appeared first on Oracle Internals.

A 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 by displaying an anti-join that was the result of transforming a “not exists” subquery and a semi-join that looked at first sight as if it were going to be the result of transforming an “exists” subquery.

As part of the dissection I reverse engineered the query into a set of tables that would allow me to reproduce the execution plan so that I could report the “final query blocks” (outline_leafs). As a brief appendix to that blog note I’m publishing here the script to create those tables and three plans that I went through to get to the plan I needed.

rem
rem     Script:         anti_semi.sql
rem     Author:         Jonathan Lewis
rem     Dated:          Apr 2020
rem
rem     Last tested 
rem             19.3.0.0
rem             12.1.0.2
rem

create table ip_spells 
partition by range (admission_dttm) interval (numtoyminterval(1,'MONTH')) (
        partition p_start values less than (to_date('01-Jan-2020'))
)
as
with g as (
        select rownum id from dual
        connect by level <= 1e4 -- > avoid wordpress format issue
)
select
        rownum                                  spell_id,
        to_char(mod(rownum,75),'FM00')          admission_method_code,
        to_date('01-jan_2020') + rownum / 1000  admission_dttm,
        to_char(mod(rownum,57),'FM00')          administrative_category_code,
        lpad(rownum,10)                         v1,
        rpad('x',100)                           padding
from
        g,g
where
        rownum <= 365000
;

alter table ip_spells add constraint sp_pk primary key(spell_id);

create table ip_episodes
as
select
        spell_id,
        spell_id        episode_id,
        1+mod(rownum,6) episode_order,
        to_char(mod(rownum,125)+ 500,'FM999')   main_specialty_code,
        v1,
        padding
from
        ip_spells
order by
        dbms_random.value
;

alter table ip_episodes add constraint ep_pk primary key(episode_id);
alter table ip_episodes add constraint ep_fk_sp foreign key (spell_id) references ip_spells;
create index idx02_ip_episodes on ip_episodes(spell_id);


create table ip_diagnosis
as
select
        episode_id,
        chr(mod(rownum,25) + 65) ||
                to_char(dbms_random.value(30,512),'FM999')      diagnosis_code,
        mod(rownum,5)                                           diagnosis_sequence,
        lpad(rownum,10)                 v1,
        rpad('x',100)                   padding
from
        (select rownum id from dual connect by level <= 5),
        ip_episodes
;

alter table ip_diagnosis add constraint di_fk_ep foreign key(episode_id) references ip_episodes;
create index idx01_ip_diagnosis on ip_diagnosis(episode_id);

The original ip_spells table was range partitioned and there was a date-based predicate in the query that encouraged me to use the date column as the partitioning column. I also decided to generate data for one year at 1,000 rows per day from the start of a year, with interval partitioning of one month to get a small number of partitions.

The original plan suggested that the number of ip_episodes was similar to the number of ip_spells, so I just used a copy of the rows from ip_spells to create ip_epsisodes, and then gave it some appropriate primary and foreign key constraints and indexes.

Finally, the ip_diagnosis table looked as if it held an average of 5 rows per ip_episodes, so I generated it from ip_episodes by joining to a 5-row set generated by the usual “connect by” trick with dual.

I’ve only got a small data set, and most of the indexes are sequence based with excellent clustering_factors, so I wasn’t going to be surprised if my data and stats didn’t immediately produce the execution plan of the original query.

Here’s the original query (just as a reminder), and the first plan I got with no hinting (running 12.1.0.2):

select
        * 
from 
        ip_spells a
where 
        not exists (
                select
                        1
                from
                        ip_episodes e
                inner join 
                        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
                        ip_episodes e
                left join 
                        ip_diagnosis d 
                on
                        d.episode_id = e.episode_id
                where
                        a.spell_id = e.spell_id
                and     (
                            e.episode_order = '1'
                        and e.main_specialty_code not in ('501','560','610')
                        and d.diagnosis_sequence = 1
                        and substr(d.diagnosis_code,1,1) <> 'O'
                        )
                )
and     substr(a.admission_method_code,1,1) = '2'                       -- 1% selectivity on substr()
and     a.admission_dttm >= to_date('01-jan-2011', 'dd-mon-yyyy')
and     a.administrative_category_code = '01'                           -- 1 / 57 by definition
;


Plan hash value: 1492475845

----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |     1 |   131 |  1299   (5)| 00:00:06 |       |       |
|*  1 |  FILTER                               |                    |       |       |            |          |       |       |
|   2 |   PARTITION RANGE ALL                 |                    |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|*  3 |    TABLE ACCESS FULL                  | IP_SPELLS          |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|   4 |   NESTED LOOPS SEMI                   |                    |     1 |    20 |    11   (0)| 00:00:01 |       |       |
|   5 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        |     1 |    10 |     4   (0)| 00:00:01 |       |       |
|*  6 |     INDEX RANGE SCAN                  | IDX02_IP_EPISODES  |     1 |       |     3   (0)| 00:00:01 |       |       |
|*  7 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       | 40931 |   399K|     7   (0)| 00:00:01 |       |       |
|*  8 |     INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS |     5 |       |     2   (0)| 00:00:01 |       |       |
|   9 |   NESTED LOOPS SEMI                   |                    |     1 |    30 |    11   (0)| 00:00:01 |       |       |
|* 10 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        |     1 |    17 |     4   (0)| 00:00:01 |       |       |
|* 11 |     INDEX RANGE SCAN                  | IDX02_IP_EPISODES  |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       | 18250 |   231K|     7   (0)| 00:00:01 |       |       |
|* 13 |     INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS |     5 |       |     2   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------

As you can see, both subqueries ran as FILTER subqueries. If I were to include the query block information you would see that operations 4 to 8 are the (transformed) “not exists” subquery, operations 9 to 13 are the transformed “exists” subquery, and operations 1 to 3 represent the main body of the query.

I needed to see the “not exists” subquery unnested and transformed into a hash anti-join, so my first attempt at hinting was to add an /*+ unnest */ hint to that subquery, producing the following plan:

Plan hash value: 147447036
------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                    |     1 |   133 |  1652   (4)| 00:00:07 |       |       |
|*  1 |  FILTER                                 |                    |       |       |            |          |       |       |
|   2 |   NESTED LOOPS ANTI                     |                    |     1 |   133 |  1641   (4)| 00:00:07 |       |       |
|   3 |    PARTITION RANGE ALL                  |                    |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|*  4 |     TABLE ACCESS FULL                   | IP_SPELLS          |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|   5 |    VIEW PUSHED PREDICATE                | VW_SQ_1            |     1 |     2 |    11   (0)| 00:00:01 |       |       |
|   6 |     NESTED LOOPS SEMI                   |                    |     1 |    20 |    11   (0)| 00:00:01 |       |       |
|   7 |      TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        |     1 |    10 |     4   (0)| 00:00:01 |       |       |
|*  8 |       INDEX RANGE SCAN                  | IDX02_IP_EPISODES  |     1 |       |     3   (0)| 00:00:01 |       |       |
|*  9 |      TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       | 40931 |   399K|     7   (0)| 00:00:01 |       |       |
|* 10 |       INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS |     5 |       |     2   (0)| 00:00:01 |       |       |
|  11 |   NESTED LOOPS SEMI                     |                    |     1 |    30 |    11   (0)| 00:00:01 |       |       |
|* 12 |    TABLE ACCESS BY INDEX ROWID BATCHED  | IP_EPISODES        |     1 |    17 |     4   (0)| 00:00:01 |       |       |
|* 13 |     INDEX RANGE SCAN                    | IDX02_IP_EPISODES  |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 14 |    TABLE ACCESS BY INDEX ROWID BATCHED  | IP_DIAGNOSIS       | 18250 |   231K|     7   (0)| 00:00:01 |       |       |
|* 15 |     INDEX RANGE SCAN                    | IDX01_IP_DIAGNOSIS |     5 |       |     2   (0)| 00:00:01 |       |       |
------------------------------------------------------------------------------------------------------------------------------

You can see the unnested subquery in the name vw_sq_1 at operation 5; but we have a nested loop anti at operation 2 when I want a hash join anti, and we also see the option “pushed predicate” for the view at operation 5.

If I published the query block information in this case you would still see 3 “final” query blocks. Operations 11 to 15 would be the “exists” subquery; operation 5 to 10 would be the query block for the non-mergeable view that the optimizer produced by unnesting the “not exists” subquery; and operations 1 to 4 represent the overall parent query block.

This example highlights a detail which is a little easy to miss in earlier discussions of the query and its plans. Some operations in a plan look as if they could be associated with two query block names – the query block for which they are the top line, and the query block by which they are used.

Taking this plan as an example, operation 5 is clearly the starting point of the query block from operations 5 to 10, and operation 11 is clearly the starting point for the query block from operations 11 to 15. On the other hand when we collapse query blocks to understand the overall structure of how the query operates we get the following plan – in which we view “Rowsource 2” and “Rowsource 3” as simple data sources in the main query block – and it would be nice to see operations 5 and 11 with the same query block name as operations 1 to 4.

---------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name            | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |                 |     1 |   133 |  1652   (4)| 00:00:07 |       |       |
|*  1 |  FILTER                                 |                 |       |       |            |          |       |       |
|   2 |   NESTED LOOPS ANTI                     |                 |     1 |   133 |  1641   (4)| 00:00:07 |       |       |
|   3 |    PARTITION RANGE ALL                  |                 |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|*  4 |     TABLE ACCESS FULL                   | IP_SPELLS       |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|   5 |    Rowsource 2 : VIEW PUSHED PREDICATE  | VW_SQ_1         |     1 |     2 |    11   (0)| 00:00:01 |       |       |
|  11 |   Rowsource 3 : NESTED LOOPS SEMI       |                 |     1 |    30 |    11   (0)| 00:00:01 |       |       |
---------------------------------------------------------------------------------------------------------------------------

We still have a little work to do to get where we want to be: the /*+ unnest */ hint has got us part way to the plan we want –  so (taking my cue from the  pushed predicate option) to get from the nested loop anti join to the hash anti join I decided to try changing the hint in the “not exists” subquery to /*+ unnest no_push_pred */ – and this is the plan that appeared as a result:

Plan hash value: 2721384176
----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name               | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |
----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |                    |     1 |   144 |  6633  (11)| 00:00:26 |       |       |
|*  1 |  FILTER                               |                    |       |       |            |          |       |       |
|*  2 |   HASH JOIN ANTI                      |                    |     1 |   144 |  6622  (11)| 00:00:26 |       |       |
|   3 |    PARTITION RANGE ALL                |                    |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|*  4 |     TABLE ACCESS FULL                 | IP_SPELLS          |    64 |  8384 |   935   (6)| 00:00:04 |     1 |1048575|
|   5 |    VIEW                               | VW_SQ_1            | 40931 |   519K|  5685  (12)| 00:00:23 |       |       |
|*  6 |     HASH JOIN                         |                    | 40931 |   799K|  5685  (12)| 00:00:23 |       |       |
|*  7 |      TABLE ACCESS FULL                | IP_DIAGNOSIS       | 40931 |   399K|  4761  (13)| 00:00:19 |       |       |
|   8 |      TABLE ACCESS FULL                | IP_EPISODES        |   365K|  3564K|   906   (5)| 00:00:04 |       |       |
|   9 |   NESTED LOOPS SEMI                   |                    |     1 |    30 |    11   (0)| 00:00:01 |       |       |
|* 10 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_EPISODES        |     1 |    17 |     4   (0)| 00:00:01 |       |       |
|* 11 |     INDEX RANGE SCAN                  | IDX02_IP_EPISODES  |     1 |       |     3   (0)| 00:00:01 |       |       |
|* 12 |    TABLE ACCESS BY INDEX ROWID BATCHED| IP_DIAGNOSIS       | 18250 |   231K|     7   (0)| 00:00:01 |       |       |
|* 13 |     INDEX RANGE SCAN                  | IDX01_IP_DIAGNOSIS |     5 |       |     2   (0)| 00:00:01 |       |       |
----------------------------------------------------------------------------------------------------------------------------

It looks the right shape, it’s got the right mechanisms in place, and (very importantly – though not a 100% guarantee) it’s got the same plan_hash_value as the orginally published plan. So at this point I felt the final query block names it reported would reflect the ones that would have been used in the original plan.

In a production system, of course, you don’t just stick a couple of hints into a query and hope it will be enough to stabilise the plan. Here’s the full set of hints that appeared in the Outline Data when I added my two hints to the query:

Outline Data
-------------
  /*+
      BEGIN_OUTLINE_DATA
      PARTIAL_JOIN(@"SEL$2B0A33EA" "D"@"SEL$2")
      USE_NL(@"SEL$2B0A33EA" "D"@"SEL$2")
      LEADING(@"SEL$2B0A33EA" "E"@"SEL$3" "D"@"SEL$2")
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2B0A33EA" "D"@"SEL$2")
      INDEX_RS_ASC(@"SEL$2B0A33EA" "D"@"SEL$2" ("IP_DIAGNOSIS"."EPISODE_ID"))
      BATCH_TABLE_ACCESS_BY_ROWID(@"SEL$2B0A33EA" "E"@"SEL$3")
      INDEX_RS_ASC(@"SEL$2B0A33EA" "E"@"SEL$3" ("IP_EPISODES"."SPELL_ID"))
      USE_HASH(@"SEL$8D33959D" "E"@"SEL$1")
      LEADING(@"SEL$8D33959D" "D"@"SEL$1" "E"@"SEL$1")
      FULL(@"SEL$8D33959D" "E"@"SEL$1")
      FULL(@"SEL$8D33959D" "D"@"SEL$1")
      PQ_FILTER(@"SEL$2B969259" SERIAL)
      USE_HASH(@"SEL$2B969259" "VW_SQ_1"@"SEL$F49409E0")
      LEADING(@"SEL$2B969259" "A"@"SEL$4" "VW_SQ_1"@"SEL$F49409E0")
      NO_ACCESS(@"SEL$2B969259" "VW_SQ_1"@"SEL$F49409E0")
      FULL(@"SEL$2B969259" "A"@"SEL$4")
      OUTLINE(@"SEL$2")
      OUTLINE(@"SEL$3")
      OUTLINE(@"SEL$4")
      OUTLINE(@"SEL$1")
      OUTLINE(@"SEL$5")
      MERGE(@"SEL$2")
      OUTLINE(@"SEL$64EAE176")
      OUTLINE(@"SEL$6")
      OUTLINE(@"SEL$F49409E0")
      MERGE(@"SEL$1")
      OUTLINE(@"SEL$8C3A16E3")
      MERGE(@"SEL$64EAE176")
      OUTLINE(@"SEL$4B169FC8")
      UNNEST(@"SEL$8C3A16E3")
      OUTLINE_LEAF(@"SEL$2B969259")
      OUTLINE_LEAF(@"SEL$8D33959D")
      OUTER_JOIN_TO_INNER(@"SEL$4B169FC8" "D"@"SEL$2")
      OUTLINE_LEAF(@"SEL$2B0A33EA")
      ALL_ROWS
      DB_VERSION('12.1.0.2')
      OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
      IGNORE_OPTIM_EMBEDDED_HINTS
      END_OUTLINE_DATA
  */

When I put my two hints into the query text and reran the test under 19.3 the FILTER operation disappeared and the “exists” subquery also unnested (to become vw_sq_2), turning into a nested loop semi-join. With the full set of 40 hints in place the plan from 12.1.0.2 re-appeared.

I did actually have an alternative strategy for the manual hint test. The plan with the nested loop anti join reported the following query block information:

Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
   1 - SEL$2B969259
   4 - SEL$2B969259 / A@SEL$4
   5 - SEL$D276C01C / VW_SQ_1@SEL$F49409E0
   6 - SEL$D276C01C
   7 - SEL$D276C01C / E@SEL$1
   8 - SEL$D276C01C / E@SEL$1
   9 - SEL$D276C01C / D@SEL$1
  10 - SEL$D276C01C / D@SEL$1
  11 - SEL$2B0A33EA
  12 - SEL$2B0A33EA / E@SEL$3
  13 - SEL$2B0A33EA / E@SEL$3

This prompted me to tell Oracle to do a hash join in query block SEL$2B969259 between A@SEL$4 and VW_SQ_1@SEL$F49409E0 (in that order) by adding a simple set of hints to the start of the query while leaving (just) the /*+ unnest */ hint in the “not exists” subquery.

select
        /*+ 
                leading (@sel$2b969259  a@sel$4  vw_sq_1@sel$f49409e0)
                use_hash(@sel$2b969259  vw_sq_1@sel$f49409e0)
                no_swap_join_inputs(@sel$2b969259 vw_sq_1@sel$f49409e0)
        */
        * 
from 
 

Again, this produced the required execution path.

It’s not always this easy to reproduce an execution plan by looking at a query text – sometimes the actual plan depends on knowing about things like unique constraints, foreign key constraints, and not null constraints. But if you can get your hands on the SQL to create the objects involved it’s usually possible to re-create the plan by hinting, which then allows you to discover how you change the hints to modify the plan.