Oracle Scratchpad

April 5, 2021

Case Study

Filed under: Oracle,Tuning — Jonathan Lewis @ 3:36 pm BST Apr 5,2021

A recent question on the Oracle Developer forum posed an interesting question on “finding the closest match” to a numeric value. The OP supplied SQL to create two tables, first a set of “valid” values each with an Id, then a set of measures. The requirement was to find, for each measure, the closest valid value and report its id.

In this note I’m going to make a few comments on three topics:

  • how the question was posed,
  • general thoughts on modelling,
  • some ideas on what to look for when testing possible solutions

We’ll start with the data (almost) as supplied:

rem
rem     Script:         closest_match.sql
rem     Author:         Jonathan Lewis / user626688
rem     Dated:          Apr 2021
rem     Purpose:        
rem
rem     Last tested 
rem             19.3.0.0
rem             12.2.0.1
rem             11.2.0.4         (with event 22829)
rem
 
create table nom_val_lkp(
        lkp_id  number       not null,
        nom_val number(3,2)  primary key
)
-- organization index
/

insert into nom_val_lkp values(1, 0.1);
insert into nom_val_lkp values(2, 0.2);
insert into nom_val_lkp values(3, 0.3);
insert into nom_val_lkp values(4, 0.4);
insert into nom_val_lkp values(5, 0.5);
insert into nom_val_lkp values(6, 0.6);
insert into nom_val_lkp values(7, 0.7);
insert into nom_val_lkp values(8, 0.8);
insert into nom_val_lkp values(9, 0.9);
commit;

create table measure_tbl(
        id              number      not null, 
        measure_val     number(3,2) not null
)
/

insert into measure_tbl values(1, 0.24);
insert into measure_tbl values(2, 0.5);
insert into measure_tbl values(3, 0.14);
insert into measure_tbl values(4, 0.68);
commit;

insert into measure_tbl values(5, 1.38);
insert into measure_tbl values(6, 0.05);
commit;


execute dbms_stats.gather_table_stats(null,'measure_tbl')
execute dbms_stats.gather_table_stats(null,'nom_val_lkp')

There are a couple of differences between the original and the SQL I’ve listed above. Given the nature of the requirement I’ve added not null constraints to both the lkp_id and nom_val columns of the “valid values” table. I think it’s also reasonable to assume that both columns outght to be (individually) unique and could both be candidate keys for the table although I’ve not bothered to add a uniqueness constraint to the lkp_id. I have made the nom_val (the interesting bit) the primary key because that’s potentially an important feature of a good solution. Obviously this is guesswork on my part, but I think they’re reasonable guesses of what the “real application” will look like and they’re details that ought to be been included in the original specification.

You’ll see that I’ve also included the option for making the table an index organized table – but that’s a generic implementation choice for small look-up tables not something that you could call an omission in the specification of requirements.

One thing to note about the nom_val_lkp table is that the nom_val is strictly constrained to be 3 digits with 2 decimal places, which means values between -9.99 to +9.99. It’s going to be a pretty small table – no more than 1,999 rows. (In “real life” it’s possible that the measure all have to be postive – and if so that’s another detail that could have gone into the specification – so the column could also have a check constraint to that effect.)

Looking at the measure_tbl (which is the “big data” table) I’ve added not null constraints to both columns; I’ve also added a couple of extra rows to the table to make sure that we can test boundary conditions when we write the final SQL statement. We’re looking for “the closest match” so we’ll be looking in the nom_val_lkp table for values above and below the measure value – so we ought to have a measure row where there is no “below” value and one with no “above” value. A common oversight in modelling is to forget about checking special cases, and simple boundary conditions are often overlooked (or inadequately covered).

Thinking about the “above / below / closest” requirement, an immediate follow-up questions springs to mind. What if there is no exact match and the valid values either side are the same distance from the measure? If there’s a tie should the query return the lower value or the higher value, or does it not matter? The specification is not complete, and the most efficient solution may depend on this detail.

Interestingly the measure_val column is constrained in exactly the same way as the nom_val column -3 digits with 2 d.p. Apparently the requirement isn’t something like “take a measurement to 6 decimal places then give me a value to 2 d.p.”; no matter how large the measure_val table gets the number of distinct values it records is pretty small – which means caching considerations could become important. With this thought in mind I added a few more lines (before gathering stats) to make multiple copies of the supplied measures data to model (approximately, and with a very large bias) a large table with a small number of distinct measures.

insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
insert into measure_tbl select * from measure_tbl;
commit;

update measure_tbl set id = rownum;
commit;

execute dbms_stats.gather_table_stats(null,'measure_tbl')

This doubling-up code resulted in a total of 6 * 1,024 = 6,144 rows in the table. I only increased the data volume after I’d checked that I had a query that produced the correct results, of course.

A possible solution

By the time I saw the thread on the Oracle forum there were already three solutions on offer, but they all took the route of using analytic functions, including one that used keep(dense_rank …), and these all involved sorting the entire measures dataset; so I thought I’d try an approach that demonstrated a completely different method that was visibly following the strategy: “for each row do two high-precision lookups”. I implemented this by joining two lateral views of the lookup table to the measures table. Since I was sitting in front of a copy of 11.2.0.4 at the time I had to set the event 22829 to enable the feature – here’s the basic code with the plan produced by 11g:

select  /*+ qb_name(main) */
        mt.id,
        mt.measure_val,
        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,
        nt_low.nom_val  low_val,
        nt_low.lkp_id   low_lkp,
        nt_high.nom_val high_val,
        nt_high.lkp_id  high_lkp 
from
        measure_tbl     mt,
        lateral(
                select
                        /*+ qb_name(low) index_rs_desc(nt (nom_val)) */
                        nt.lkp_id, nt.nom_val
                from    nom_val_lkp nt
                where   nt.nom_val <= mt.measure_val
                and     rownum = 1
        )(+) nt_low,
        lateral(
                select
                        /*+ qb_name(high) index_rs_asc(nt (nom_val)) */
                        nt.lkp_id, nt.nom_val
                from    nom_val_lkp nt
                where   nt.nom_val >= mt.measure_val
                and     rownum = 1
        ) (+) nt_high
/

        ID MEASURE_VAL     LKP_ID    LOW_VAL    LOW_LKP   HIGH_VAL   HIGH_LKP
---------- ----------- ---------- ---------- ---------- ---------- ----------
         1         .24          2         .2          2         .3          3
         2          .5          5         .5          5         .5          5
         3         .14          1         .1          1         .2          2
         4         .68          7         .6          6         .7          7
         5        1.38          9         .9          9
         6         .05          1                               .1          1


6 rows selected.

You’ll notice that (for debugging purposes) I’ve included columns in my output for the lkp_id and nom_val just lower than (or matching) and just higher than (or matching) the measure_val. The blanks this produces in two of the rows conveniently highlights the cases where the measure is “out of bounds”.

With my tiny data set I had to include the index_rs_desc() hint. Of course I should really have included an “order by” clause in the two subqueries and used an extra layer of inline views to introduce the rownum = 1 predicate, viz:

        lateral(
                select  * 
                from    (
                        select  /*+ qb_name(low) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        order by
                                nom_val desc
                )
                where   rownum = 1
        )(+) nt_low,

There were two reasons I didn’t do this: first I wanted to keep the code short, secondly it wouldn’t have worked with 11g because it was only in 12c that a correlated subquery could correlate more than one level up – the predicate referencing mt.measure_val would have raised error “ORA-00904: invalid identifier”.

If you’re not familiar with lateral views, the idea is quite simple: as with any inline view in the from clause it’s just a query that returns a result set that looks like a table, but it has the special condition that the predicafes in the query can reference columns from tables (or views) that have appeared further to the left in (or, equivalently, further up) the from clause. In this case both of my inline views query nom_val_lkp and both of them reference a column in measure_tbl which was the first table in the from clause.

There are two distinguishing details that are a consequence of the lateral view. First, the view effectively has a join to the driving table built into it so my main query doesn’t have any where clause predicates joining the views to the rest of the query. Se,condly I want to do outer joins (to deal with the cases where there isn’t a nom_val higher/ lower than the measure_val) so in the absence of a join predicate in the main query the necessary syntax simply adds Oracle’s traditional “(+)” to the lateral() operator itself. (If you want to go “full-ANSI” you would use outer apply() instead of lateral()(+) at this point – but 11g doesn’t support outer apply().

Here’s the execution plan from 11g for this query – I’ve enabled rowsource execution stats and pulled the plan from memory using the ‘allstats last’ format option:

select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |      6 |00:00:00.01 |      29 |
|   1 |  NESTED LOOPS OUTER              |              |      1 |      6 |      6 |00:00:00.01 |      29 |
|   2 |   NESTED LOOPS OUTER             |              |      1 |      6 |      6 |00:00:00.01 |      18 |
|   3 |    TABLE ACCESS FULL             | MEASURE_TBL  |      1 |      6 |      6 |00:00:00.01 |       7 |
|   4 |    VIEW                          |              |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  5 |     COUNT STOPKEY                |              |      6 |        |      5 |00:00:00.01 |      11 |
|   6 |      TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP  |      6 |      2 |      5 |00:00:00.01 |      11 |
|*  7 |       INDEX RANGE SCAN DESCENDING| SYS_C0072287 |      6 |      6 |      5 |00:00:00.01 |       6 |
|   8 |   VIEW                           |              |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  9 |    COUNT STOPKEY                 |              |      6 |        |      5 |00:00:00.01 |      11 |
|  10 |     TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|* 11 |      INDEX RANGE SCAN            | SYS_C0072287 |      6 |      4 |      5 |00:00:00.01 |       6 |
-----------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   5 - filter(ROWNUM=1)
   7 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
       filter("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
   9 - filter(ROWNUM=1)
  11 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")


As you can see we’ve done a full tablescan of measure_tbl, then performed an outer join to each of two (unnamed) views for each row, and each time we’ve accessed a view we’ve done an index range scan (descending in one case) into nom_val_lkp. passing in (according to the Predicate Information) the measure_val from measure_tbl.

It’s a little oddity I hadn’t noticed before that the ascending and descending range scans behave slightly differently – the descending range scan says we’ve used the predicate as both an access and a filter predicate. I’ll have to check whether this is always the case or whether it’s version-dependent or whether it’s only true under some conditions.

The only other detail to mention is the expression I’ve used to report the closest match – which is a little messy to allow for “out of range” measures::

        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,

This case expression says that if the higher nom_val is closer to (or, to be precise, not further from) the meause_val than the lower nom_val then report the higher lkp_id. otherwise report the lower lkp_id. The ordering of the comparison means that when the differences are the same the higher value will always be reported; and the “cross-over” use of the nvl() function ensures that when the measure_val is out of range (which means one of the nom_val subqueries will have returned null) we see the nom_val that’s at the end of the range rather than a null.

Some bad news

At first sight the lateral() view looks as if it might be a candidate for scalar subquery caching – so when I create multiple copies of the 6 rows in the measure_tbl and run my query against the expanded data set I might hope to get excellent performance because Oracle might only have to call each lateral view once and and cache the subquery inputs and results from that point onwards. But here are the stats I get from the 11g plan after exanding the data to 6,144 rows:

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                        | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                 |              |      1 |        |   6144 |00:00:00.82 |   22953 |
|   1 |  NESTED LOOPS OUTER              |              |      1 |   6144 |   6144 |00:00:00.82 |   22953 |
|   2 |   NESTED LOOPS OUTER             |              |      1 |   6144 |   6144 |00:00:00.47 |   11689 |
|   3 |    TABLE ACCESS FULL             | MEASURE_TBL  |      1 |   6144 |   6144 |00:00:00.03 |     425 |
|   4 |    VIEW                          |              |   6144 |      1 |   5120 |00:00:00.28 |   11264 |
|*  5 |     COUNT STOPKEY                |              |   6144 |        |   5120 |00:00:00.20 |   11264 |
|   6 |      TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP  |   6144 |      2 |   5120 |00:00:00.12 |   11264 |
|*  7 |       INDEX RANGE SCAN DESCENDING| SYS_C0072291 |   6144 |      5 |   5120 |00:00:00.04 |    6144 |
|   8 |   VIEW                           |              |   6144 |      1 |   5120 |00:00:00.32 |   11264 |
|*  9 |    COUNT STOPKEY                 |              |   6144 |        |   5120 |00:00:00.19 |   11264 |
|  10 |     TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP  |   6144 |      2 |   5120 |00:00:00.11 |   11264 |
|* 11 |      INDEX RANGE SCAN            | SYS_C0072291 |   6144 |      3 |   5120 |00:00:00.04 |    6144 |
-----------------------------------------------------------------------------------------------------------


Look at the Starts column: the two views were called once each for every single row in the expanded measure_tbl, there’s no scalar subquery caching going on.

Bug time (1)

Of course, this is 11g and I’ve enabled lateral views by setting an event; it’s not an officially supported feature so maybe if I upgrade to 12c (or 19c), where the feature is official, Oracle will do better.

Here are the results of the original query against the original data set in 12c and 19c:

        ID MEASURE_VAL     LKP_ID    LOW_VAL    LOW_LKP   HIGH_VAL   HIGH_LKP
---------- ----------- ---------- ---------- ---------- ---------- ----------
         6         .05          1                               .1          1
         3         .14          1         .1          1
         1         .24          1         .1          1
         2          .5          1         .1          1
         4         .68          1         .1          1
         5        1.38          1         .1          1

On the upgrade I’ve got the wrong results! So what does the execution plan look like:

--------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation               | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT        |                 |      1 |        |      6 |00:00:00.01 |      17 |       |       |          |
|   1 |  MERGE JOIN OUTER       |                 |      1 |      6 |      6 |00:00:00.01 |      17 |       |       |          |
|   2 |   SORT JOIN             |                 |      1 |      6 |      6 |00:00:00.01 |      12 |  2048 |  2048 | 2048  (0)|
|   3 |    MERGE JOIN OUTER     |                 |      1 |      6 |      6 |00:00:00.01 |      12 |       |       |          |
|   4 |     SORT JOIN           |                 |      1 |      6 |      6 |00:00:00.01 |       7 |  2048 |  2048 | 2048  (0)|
|   5 |      TABLE ACCESS FULL  | MEASURE_TBL     |      1 |      6 |      6 |00:00:00.01 |       7 |       |       |          |
|*  6 |     SORT JOIN           |                 |      6 |      1 |      5 |00:00:00.01 |       5 |  2048 |  2048 | 2048  (0)|
|   7 |      VIEW               | VW_DCL_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|*  8 |       COUNT STOPKEY     |                 |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|   9 |        TABLE ACCESS FULL| NOM_VAL_LKP     |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|* 10 |   SORT JOIN             |                 |      6 |      1 |      1 |00:00:00.01 |       5 |  2048 |  2048 | 2048  (0)|
|  11 |    VIEW                 | VW_DCL_A18161FF |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
|* 12 |     COUNT STOPKEY       |                 |      1 |        |      1 |00:00:00.01 |       5 |       |       |          |
|  13 |      TABLE ACCESS FULL  | NOM_VAL_LKP     |      1 |      1 |      1 |00:00:00.01 |       5 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   6 - access(INTERNAL_FUNCTION("NOM_VAL")<=INTERNAL_FUNCTION("MT"."MEASURE_VAL"))
       filter(INTERNAL_FUNCTION("NOM_VAL")<=INTERNAL_FUNCTION("MT"."MEASURE_VAL"))
   8 - filter(ROWNUM=1)
  10 - access("NOM_VAL">="MT"."MEASURE_VAL")
       filter("NOM_VAL">="MT"."MEASURE_VAL")
  12 - filter(ROWNUM=1)


Check what’s appeared in the Name for the view operations 7 and 11: VW_DCL_ A18161FF (DCL = “decorrelate”), I was expecting to see names starting with VW_LAT (LAT = “lateral”). And then I remembered reading this article by Sayan Malakshinov – Oracle (12c+) can decorrelate lateral views but gets the wrong results with rownum. So let’s add in a few hints to avoid decorrelation and check the results and execution plan.

-------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                                | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                         |                 |      1 |        |      6 |00:00:00.01 |      30 |       |       |          |
|   1 |  MERGE JOIN OUTER                        |                 |      1 |      6 |      6 |00:00:00.01 |      30 |       |       |          |
|   2 |   MERGE JOIN OUTER                       |                 |      1 |      6 |      6 |00:00:00.01 |      19 |       |       |          |
|   3 |    TABLE ACCESS FULL                     | MEASURE_TBL     |      1 |      6 |      6 |00:00:00.01 |       8 |       |       |          |
|   4 |    BUFFER SORT                           |                 |      6 |      1 |      5 |00:00:00.01 |      11 |  2048 |  2048 | 2048  (0)|
|   5 |     VIEW                                 | VW_LAT_D77DA787 |      6 |      1 |      5 |00:00:00.01 |      11 |       |       |          |
|*  6 |      COUNT STOPKEY                       |                 |      6 |        |      5 |00:00:00.01 |      11 |       |       |          |
|   7 |       TABLE ACCESS BY INDEX ROWID BATCHED| NOM_VAL_LKP     |      6 |      2 |      5 |00:00:00.01 |      11 |       |       |          |
|*  8 |        INDEX RANGE SCAN                  | SYS_C0055681    |      6 |      3 |      5 |00:00:00.01 |       6 |       |       |          |
|   9 |   BUFFER SORT                            |                 |      6 |      1 |      5 |00:00:00.01 |      11 |  2048 |  2048 | 2048  (0)|
|  10 |    VIEW                                  | VW_LAT_D77DA787 |      6 |      1 |      5 |00:00:00.01 |      11 |       |       |          |
|* 11 |     COUNT STOPKEY                        |                 |      6 |        |      5 |00:00:00.01 |      11 |       |       |          |
|  12 |      TABLE ACCESS BY INDEX ROWID BATCHED | NOM_VAL_LKP     |      6 |      2 |      5 |00:00:00.01 |      11 |       |       |          |
|* 13 |       INDEX RANGE SCAN DESCENDING        | SYS_C0055681    |      6 |      5 |      5 |00:00:00.01 |       6 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   6 - filter(ROWNUM=1)
   8 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")
  11 - filter(ROWNUM=1)
  13 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
       filter("NT"."NOM_VAL"<="MT"."MEASURE_VAL")

Blocking decorrelation was sufficient to get the correct result but there’s still a funny little glitch in the execution plan: why do we have merge join (outer) for operations 1 and 2?

It’s not quite the threat you might think; we’re not multiplying up rows catastrophically. For each row in measures_tbl Oracle does a Cartesian merge join to (at most) one row in each view – so there’s no accidental explosion in data volume, and there’s no real sorting. Nevertheless there may be unnecessary CPU usage so let’s add a few more hints to try and get a nested loop by adding the following hints to the start of the query:

        /*+
                qb_name(main)
                leading(@main mt@main nt_high@main nt_low@main)
                use_nl(@main nt_high@main)
                use_nl(@main nt_low@main)
        */

I was a little surprised at the benefit – roughly a 30% saving on CPU for the same data set.

But there’s more to investigate – I didn’t like the index hints that I’d had to use in 11g, but 12c allows for the more complex “two layer” lateral subquery with its deeply correlated predicate – so what happens if I use the following corrected query (with minimal hinting) in 12c or 19c:

select
        /*+
                qb_name(main)
--              leading(@main mt@main nt_high@main nt_low@main)
--              use_nl(@main nt_high@main)
--              use_nl(@main nt_low@main)
        */
        mt.id,
        mt.measure_val,
        case
                when
                        nt_high.nom_val - mt.measure_val <=
                        mt.measure_val - nt_low.nom_val
                then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                else    nvl(nt_low.lkp_id,nt_high.lkp_id)
        end     lkp_id,
        nt_low.nom_val  low_val,
        nt_low.lkp_id   low_lkp,
        nt_high.nom_val high_val,
        nt_high.lkp_id  high_lkp 
from
        measure_tbl     mt,
        lateral(
                select  *
                from    (
                        select  /*+ qb_name(low) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        order by
                                nom_val desc
                        )
                where   rownum = 1
        )(+) nt_low,
        lateral(
                select  *
                from    (
                        select  /*+ qb_name(high) */
                                nt.lkp_id, nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        order by
                                nom_val
                )
                where   rownum = 1
        )(+) nt_high
/

First – Oracle doesn’t use decorrelation so I get the right results; secondly Oracle uses the correct index descending without hinting, which is an important part of getting the right results. Unfortunately I still see merge joins unless I include the use_nl() hints (with the leading() hint as an extra safety barrier) to get that 30% reduction in CPU usage.

The sad news is that I still don’t see scalar subquery caching. If I have 6,144 rows in measure_tbl I still see 6,144 executions of both the lateral subqueries.

Since 12c onwards supports “outer apply” it’s worth testing to see what happens if I replace my lateral()(+) operator with the outer apply() mechanism. It works, but behaves very much like the lateral approach (including the unexpected merge joins unless hinted), except it introduces another layer of lateral joins. Here’s the plan (12c and 19c) with 6,144 rows:

--------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                           | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
--------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                    |                 |      1 |        |   6144 |00:00:00.14 |   22954 |       |       |          |
|   1 |  MERGE JOIN OUTER                   |                 |      1 |   6144 |   6144 |00:00:00.14 |   22954 |       |       |          |
|   2 |   MERGE JOIN OUTER                  |                 |      1 |   6144 |   6144 |00:00:00.08 |   11690 |       |       |          |
|   3 |    TABLE ACCESS FULL                | MEASURE_TBL     |      1 |   6144 |   6144 |00:00:00.01 |     426 |       |       |          |
|   4 |    BUFFER SORT                      |                 |   6144 |      1 |   5120 |00:00:00.06 |   11264 |  2048 |  2048 | 2048  (0)|
|   5 |     VIEW                            | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|   6 |      VIEW                           | VW_LAT_A18161FF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|*  7 |       COUNT STOPKEY                 |                 |   6144 |        |   5120 |00:00:00.03 |   11264 |       |       |          |
|   8 |        VIEW                         |                 |   6144 |      2 |   5120 |00:00:00.03 |   11264 |       |       |          |
|   9 |         TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP     |   6144 |      6 |   5120 |00:00:00.02 |   11264 |       |       |          |
|* 10 |          INDEX RANGE SCAN DESCENDING| SYS_C0023500    |   6144 |      2 |   5120 |00:00:00.01 |    6144 |       |       |          |
|  11 |   BUFFER SORT                       |                 |   6144 |      1 |   5120 |00:00:00.06 |   11264 |  2048 |  2048 | 2048  (0)|
|  12 |    VIEW                             | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|  13 |     VIEW                            | VW_LAT_E88661A9 |   6144 |      1 |   5120 |00:00:00.04 |   11264 |       |       |          |
|* 14 |      COUNT STOPKEY                  |                 |   6144 |        |   5120 |00:00:00.03 |   11264 |       |       |          |
|  15 |       VIEW                          |                 |   6144 |      1 |   5120 |00:00:00.02 |   11264 |       |       |          |
|  16 |        TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP     |   6144 |      1 |   5120 |00:00:00.02 |   11264 |       |       |          |
|* 17 |         INDEX RANGE SCAN            | SYS_C0023500    |   6144 |      4 |   5120 |00:00:00.01 |    6144 |       |       |          |
--------------------------------------------------------------------------------------------------------------------------------------------


Predicate Information (identified by operation id):
---------------------------------------------------
   7 - filter(ROWNUM=1)
  10 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
  14 - filter(ROWNUM=1)
  17 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")

Note operations 5 and 6, then 12 and 13: the “ANSI” syntax outer apply seems to be another case of Oracle doing more work because it has to transform the query before optimising.

A Traditional Solution

Having worked through a few of the newer mechanisms in Oracle, why not think back to how the same pattern of implementation could have been achieved in older versions of Oracle. What’s wrong, for example, with using scalar subqueries in the select list? If we can expect plenty of scalar subquery caching this might be a very effective way of writing the query.

The immediate problem, though, is that scalar subqueries in the select list only allow one column to be returned (unless you want to fake things through by playing nasty games with user-defined types). So our two lateral views will have to change to four scalar subqueres to get all the data we need.

Here’s a possible solution (I’ve stuck with the hinted shorter, but bad practice, “first row” mechanism for compactness) – with execution stats:

select
        id,
        measure_val,
        case
                when
                        nt_high_nom_val - measure_val <=
                        measure_val - nt_low_nom_val
                then    nvl(nt_high_lkp_id,nt_low_lkp_id)
                else    nvl(nt_low_lkp_id,nt_high_lkp_id)
        end     lkp_id,
        nt_low_nom_val,
        nt_low_lkp_id,
        nt_high_nom_val,
        nt_high_lkp_id
from    (
        select
                mt.id,
                mt.measure_val,
                (
                        select
                                /*+ index_rs_asc(nt (nom_val)) */
                                nt.lkp_id
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        and     rownum = 1
                ) nt_high_lkp_id,
                (
                        select
                                /*+ index_rs_asc(nt (nom_val)) */
                                nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val >= mt.measure_val
                        and     rownum = 1
                ) nt_high_nom_val,
                (
                        select
                                /*+ index_rs_desc(nt (nom_val)) */
                                nt.lkp_id
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        and     rownum = 1
                ) nt_low_lkp_id,
                (
                        select
                                /*+ index_rs_desc(nt (nom_val)) */
                                nt.nom_val
                        from    nom_val_lkp nt
                        where   nt.nom_val <= mt.measure_val
                        and     rownum = 1
                ) nt_low_nom_val
        from
                measure_tbl     mt
        )
/

------------------------------------------------------------------------------------------------------------------
| Id  | Operation                               | Name         | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                        |              |      1 |        |   6144 |00:00:00.01 |     426 |
|*  1 |  COUNT STOPKEY                          |              |      6 |        |      5 |00:00:00.01 |       6 |
|*  2 |   INDEX RANGE SCAN                      | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  3 |   COUNT STOPKEY                         |              |      6 |        |      5 |00:00:00.01 |       6 |
|*  4 |    INDEX RANGE SCAN DESCENDING          | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  5 |    COUNT STOPKEY                        |              |      6 |        |      5 |00:00:00.01 |      11 |
|   6 |     TABLE ACCESS BY INDEX ROWID BATCHED | NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|*  7 |      INDEX RANGE SCAN                   | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|*  8 |     COUNT STOPKEY                       |              |      6 |        |      5 |00:00:00.01 |      11 |
|   9 |      TABLE ACCESS BY INDEX ROWID BATCHED| NOM_VAL_LKP  |      6 |      1 |      5 |00:00:00.01 |      11 |
|* 10 |       INDEX RANGE SCAN DESCENDING       | SYS_C0023507 |      6 |      1 |      5 |00:00:00.01 |       6 |
|  11 |  TABLE ACCESS FULL                      | MEASURE_TBL  |      1 |   6144 |   6144 |00:00:00.01 |     426 |
------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter(ROWNUM=1)
   2 - access("NT"."NOM_VAL">=:B1)
   3 - filter(ROWNUM=1)
   4 - access("NT"."NOM_VAL"<=:B1)
       filter("NT"."NOM_VAL"<=:B1)
   5 - filter(ROWNUM=1)
   7 - access("NT"."NOM_VAL">=:B1)
   8 - filter(ROWNUM=1)
  10 - access("NT"."NOM_VAL"<=:B1)
       filter("NT"."NOM_VAL"<=:B1)

I’ve left the index hints in place in this example so that the code can run on 11g and earlier (without setting any special events, of course); but in 12c and 19c if you replace the subqueries with the double-layer subqueries (inline order by, then rownum = 1) as shown further up the page the hints (specifically the descending hints) are no longer necessary.

The key performance benefit of this approach is visible in the Starts column – although I now have 4 subqueries to run (which should mean doing more work) each one runs only once thanks to an extremely “lucky” level of scalar subquery caching.

This, really, is where this note takes us back to the beginning. Will this be a fantastic solution for the end-user, or does the pattern of the data mean that it’s going to be a total disaster. It’s nice to see the SQL that defines the tables and supplies a bit of test data – but there’s not point in trying to provide a solution without a better idea of what the data really looks like and what the critical usage is in production.

Bug time (2)

Nothing’s perfect, of course – and even though this last SQL statement is pretty simple and its execution plan is (for the right data pattern) very efficient, the shape of the plan is wrong – and in more complex plans you could be fooled into thinking that Oracle isn’t doing what you want it do.

Operations 1,3,5,8 and 11 should all be at the same depth (you’ll find that they all have parent_id = 0 if you look at the underlying data in v$sql_plan): there’s a defect in Oracle’s calculation of the depth column of v$sql_plan (et. al.) that introduces a pattern of indentation that shouldn’t be there.

Summary

This has been a fairly informal ramble through the playing around that I did after I read the original post. It holds some comments about the way the question was asked, the test data as supplied and corrected, and the observations and tweaks as the testing progressed.

On the plus size, the OP has supplied code to create and populate a model, and described what they wanted to see as a result. However the requirement didn’t mention (and the model therefore didn’t cater for) a couple of special cases. There were also a few cases where unique and mandatory columns were likely to be appropriate but were not mentioned, even though they could affect the correctness or performance of any suggested solutions.

More importantly, although the model implied some fairly narrow restrictions on what the production data might look like this information wasn’t presented explcitily, and there were no comments about the ultimate scale and distribution patterns of the data that might give some clues about the most appropriate features of SQL to use.

5 Comments »

  1. Hello Jonathan,

    Thanks a lot for another excellent post, as usual :)

    Just a short comment regarding the “double VW_LAT” views that are shown in the execution plan when using OUTER APPLY.

    It just happens that I checked this issue a few days ago following a Twitter post:

    https://twitter.com/mentzel_iudith/status/1377199609878024196.

    As remarked by Oren Nakdimon, Oracle always transforms a query using CROSS APPLY into one using (one single) LATERAL .

    But, strangely, when it transforms a query using OUTER APPLY, it uses two layers of LATERAL, as we can see from the following small
    example (tested in livesql.oracle.com – Oracle 19.8.0.0.0):

    declare
       p_clob  clob := 'select employee_id, 
                                           department_name
                                from hr.employees  e
                                        outer apply (select department_name
                                                            from hr.departments  d
                                                            where d.department_id = e.department_id)' ;
       v_clob  clob;
    begin
       dbms_utility.expand_sql_text(input_sql_text => p_clob, output_sql_text => v_clob);
       dbms_output.put_line(v_clob);
    end;
    /
    
    SELECT "A1"."EMPLOYEE_ID_0" "EMPLOYEE_ID","A1"."DEPARTMENT_NAME_2" "DEPARTMENT_NAME" FROM  
     (SELECT "A3"."EMPLOYEE_ID" "EMPLOYEE_ID_0","A3"."DEPARTMENT_ID" "DEPARTMENT_ID","A2"."DEPARTMENT_NAME_0" "DEPARTMENT_NAME_2" 
      FROM "HR"."EMPLOYEES" "A3", LATERAL( (SELECT "A4"."DEPARTMENT_NAME_0" "DEPARTMENT_NAME_0" 
                                             FROM  LATERAL( (SELECT "A5"."DEPARTMENT_NAME" "DEPARTMENT_NAME_0" FROM "HR"."DEPARTMENTS" "A5" 
                                             WHERE "A5"."DEPARTMENT_ID"="A3"."DEPARTMENT_ID")) "A4" 
                                            WHERE 1=1))(+) "A2"
     ) "A1"
    
    

    I could not see the real reason for this “duplication”, I guess that it probably resides in the way in which the various query transformation steps are applied/coded internally.

    Best Regards,
    Iudith Mentzel

    Comment by Iudith Mentzel — April 5, 2021 @ 6:39 pm BST Apr 5,2021 | Reply

    • Iudith,

      Thanks for the comment – and the link to that twitter discussion.

      It’s quite funny (and highly relevant to my comments about knowing the data and know the processing requirements) that Chris’ example is about using a single lateral() view to replace multiple scalar subqueries when my example explains why doing exactly the opposite could be more efficient.

      Regards
      Jonathan Lewis

      I forgot to say anything about the doubling of the VW_LAT.

      Going back 10 years the note I wrote about the lateral join and event 22829 showed Oracle transformating an “ANSI” outer join to a lateral join. So it crossed my mind to wonder whether that particular code path had been called as a heuristic transformation of an outer join before the code for handling the explicit lateral view was invoked.

      Comment by Jonathan Lewis — April 6, 2021 @ 11:00 am BST Apr 6,2021 | Reply

  2. A little side note: OUTER APPLY is not “full ANSI” (nor is CROSS APPLY). The ANSI SQL equivalent would be LEFT JOIN LATERAL or CROSS JOIN LATERAL)

    I think the APPLY variant was only added by Oracle to be compatible with Microsoft’s non-standard implementation of lateral joins.

    Comment by Hans — April 6, 2021 @ 6:25 am BST Apr 6,2021 | Reply

  3. Hans,

    Thanks for the comment, it’s an interesting detail.
    You’ve prompted me to run up another little test case – using LEFT JOIN LATERAL. I’m not certain I’ve done it totally correctly, or whether there’s another lateral() option, since I’ve had to add the clause “ON 1 = 1” to make the join legal:

    
    select
            /*+
                    qb_name(main) 
                    leading(@sel$e594d506 mt@sel$3 "from$_subquery$_010"@sel$3 "from$_subquery$_011"@sel$6)
                    use_nl(@sel$e594d506 "from$_subquery$_010"@sel$3)
                    use_nl(@sel$e594d506 "from$_subquery$_011"@sel$6)
            */
    
            mt.id,
            mt.measure_val,
            case
                    when
                            nt_high.nom_val - mt.measure_val <=
                            mt.measure_val - nt_low.nom_val
                    then    nvl(nt_high.lkp_id,nt_low.lkp_id)
                    else    nvl(nt_low.lkp_id,nt_high.lkp_id)
            end     lkp_id,
            nt_low.nom_val  low_val,
            nt_low.lkp_id   low_lkp,
            nt_high.nom_val high_val,
            nt_high.lkp_id  high_lkp 
    from
            measure_tbl     mt
            left join lateral(
                    select  *
                    from    (
                            select
                                    /*+
                                            qb_name(low) 
                                    */
                                    nt.lkp_id, nt.nom_val
                            from    nom_val_lkp nt
                            where   nt.nom_val <= mt.measure_val
                            order by
                                    nt.nom_val desc
                    )
                    where   rownum = 1
            ) nt_low
            on 1 = 1
            left join lateral(
                    select  *
                    from    (
                            select
                                    /*+ 
                                            qb_name(high) 
                                    */
                                    nt.lkp_id, nt.nom_val
                            from    nom_val_lkp nt
                            where   nt.nom_val >= mt.measure_val
                            order by
                                    nt.nom_val
                            )
                    where   rownum = 1
            ) nt_high
            on 1 = 1
    /
    
    
    

    I’ve used the double-layer query in the lateral views so didn’t need to hint the use of indexes, but I still had to hint the nested loop if I wanted to avoid the (Cartesian) merge joins. Unfortunately I also had to spend some time on trial and error to get the derived query block names right because of the extra layers of transformation that Oracle created. Here’s the final plan (from 19.3)

    ----------------------------------------------------------------------------------------------------------------
    | Id  | Operation                          | Name            | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
    ----------------------------------------------------------------------------------------------------------------
    |   0 | SELECT STATEMENT                   |                 |      1 |        |   6144 |00:00:00.11 |   22606 |
    |   1 |  NESTED LOOPS OUTER                |                 |      1 |   6144 |   6144 |00:00:00.11 |   22606 |
    |   2 |   NESTED LOOPS OUTER               |                 |      1 |   6144 |   6144 |00:00:00.08 |   11342 |
    |   3 |    TABLE ACCESS FULL               | MEASURE_TBL     |      1 |   6144 |   6144 |00:00:00.01 |      78 |
    |   4 |    VIEW                            | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.07 |   11264 |
    |   5 |     VIEW                           | VW_LAT_A18161FF |   6144 |      1 |   5120 |00:00:00.07 |   11264 |
    |*  6 |      COUNT STOPKEY                 |                 |   6144 |        |   5120 |00:00:00.07 |   11264 |
    |   7 |       VIEW                         |                 |   6144 |      2 |   5120 |00:00:00.06 |   11264 |
    |   8 |        TABLE ACCESS BY INDEX ROWID | NOM_VAL_LKP     |   6144 |      6 |   5120 |00:00:00.06 |   11264 |
    |*  9 |         INDEX RANGE SCAN DESCENDING| SYS_C0023704    |   6144 |      2 |   5120 |00:00:00.05 |    6144 |
    |  10 |   VIEW                             | VW_LAT_F8C248CF |   6144 |      1 |   5120 |00:00:00.03 |   11264 |
    |  11 |    VIEW                            | VW_LAT_E88661A9 |   6144 |      1 |   5120 |00:00:00.02 |   11264 |
    |* 12 |     COUNT STOPKEY                  |                 |   6144 |        |   5120 |00:00:00.02 |   11264 |
    |  13 |      VIEW                          |                 |   6144 |      1 |   5120 |00:00:00.01 |   11264 |
    |  14 |       TABLE ACCESS BY INDEX ROWID  | NOM_VAL_LKP     |   6144 |      1 |   5120 |00:00:00.01 |   11264 |
    |* 15 |        INDEX RANGE SCAN            | SYS_C0023704    |   6144 |      4 |   5120 |00:00:00.01 |    6144 |
    ----------------------------------------------------------------------------------------------------------------
    
    Predicate Information (identified by operation id):
    ---------------------------------------------------
    
       6 - filter(ROWNUM=1)
       9 - access("NT"."NOM_VAL"<="MT"."MEASURE_VAL")
      12 - filter(ROWNUM=1)
      15 - access("NT"."NOM_VAL">="MT"."MEASURE_VAL")
    
    
    
    

    Regards
    Jonathan Lewis

    Comment by Jonathan Lewis — April 6, 2021 @ 11:40 am BST Apr 6,2021 | Reply

    • It’s probably worth mentioning that I had to quote the “from$_subquery$_nnn” query block names and keep them in lower case. All the other hints and query block names could be upper or lower, unquoted. But these internally generated names HAD to be exactly correct.

      Regards
      Jonathan Lewis

      I forgot to highlight the little detail that in this example we see, once again, the doubled VW_LAT view operation which doesn’t appear when using the “traditional” outer join (+) with the lateral view.

      Comment by Jonathan Lewis — April 6, 2021 @ 11:42 am BST Apr 6,2021 | 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.

Website Powered by WordPress.com.