Oracle Scratchpad

June 26, 2019

Glitches

Filed under: 12c,Bugs,Execution plans,Function based indexes,Indexing,Oracle — Jonathan Lewis @ 5:11 pm BST Jun 26,2019

Here’s a question just in from Oracle-L that demonstrates the pain of assuming things work consistently when sometimes Oracle development hasn’t quite finished a bug fix or enhancement. Here’s the problem – which starts from the “scott.emp” table (which I’m not going to create in the code below):

rem
rem     Script:         fbi_fetch_first_bug.sql
rem     Author:         Jonathan Lewis
rem     Dated:          June 2019
rem 

-- create and populate EMP table from SCOTT demo schema

create index e_sort1 on emp (job, hiredate);
create index e_low_sort1 on emp (lower(job), hiredate);

set serveroutput off
alter session set statistics_level = all;
set linesize 156
set pagesize 60

select * from emp where job='CLERK'         order by hiredate fetch first 2 rows only; 
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last outline alias'));

select * from emp where lower(job)='clerk' order by hiredate fetch first 2 rows only; 
select * from table(dbms_xplan.display_cursor(null,null,'cost allstats last outline alias'));

Both queries use the 12c “fetch first” feature to select two rows from the table. We have an index on (job, hiredate) and a similar index on (lower(job), hiredate), and given the similarity of the queries and the respective indexes (get the first two rows by hiredate where job/lower(job) is ‘CLERK’/’clerk’) we might expect to see the same execution plan in both cases with the only change being the choice of index used. But here are the plans:


select * from emp where job='CLERK'         order by hiredate fetch
first 2 rows only

Plan hash value: 92281638

----------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name    | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
----------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |         |      1 |        |     2 (100)|      2 |00:00:00.01 |       4 |
|*  1 |  VIEW                         |         |      1 |      2 |     2   (0)|      2 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY       |         |      1 |      3 |     2   (0)|      2 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP     |      1 |      3 |     2   (0)|      3 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | E_SORT1 |      1 |      3 |     1   (0)|      3 |00:00:00.01 |       2 |
----------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."HIREDATE")<=2)
   4 - access("JOB"='CLERK')


select * from emp where lower(job)='clerk' order by hiredate fetch
first 2 rows only

Plan hash value: 4254915479

-------------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-------------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |             |      1 |        |     1 (100)|      2 |00:00:00.01 |       2 |       |       |          |
|*  1 |  VIEW                                 |             |      1 |      2 |     1   (0)|      2 |00:00:00.01 |       2 |       |       |          |
|*  2 |   WINDOW SORT PUSHED RANK             |             |      1 |      1 |     1   (0)|      2 |00:00:00.01 |       2 |  2048 |  2048 | 2048  (0)|
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| EMP         |      1 |      1 |     1   (0)|      4 |00:00:00.01 |       2 |       |       |          |
|*  4 |     INDEX RANGE SCAN                  | E_LOW_SORT1 |      1 |      1 |     1   (0)|      4 |00:00:00.01 |       1 |       |       |          |
-------------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."HIREDATE")<=2)
   4 - access("EMP"."SYS_NC00009$"='clerk')


As you can see, with the “normal” index Oracle is able to walk the index “knowing” that the data is appearing in order, and stopping as soon as possible (almost) – reporting the WINDOW operation as “WINDOW NOSORT STOPKEY”. On the other hand with the function-based index Oracle retrieves all the data by index, sorts it, then applies the ranking requirement – reporting the WINDOW operation as “WINDOW SORT PUSHED RANK”.

Clearly it’s not going to make a lot of difference to performance in this tiny case, but there is a threat that the whole data set for ‘clerk’ will be accessed – and that’s the first performance threat, with the additional threat that the optimizer might decide that a full tablescan would be more efficient than the index range scan.

Can we fix it ?

Yes, Bob, we can. The problem harks back to a limitation that probably got fixed some time between 10g and 11g – here are two, simpler, queries against the emp table and the two new indexes, each with the resulting execution plan when run under Oracle 10.2.0.5:


select ename from emp where       job  = 'CLERK' order by hiredate;
select ename from emp where lower(job) = 'clerk' order by hiredate;

---------------------------------------------------------------------------------------
| Id  | Operation                   | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |         |     3 |    66 |     2   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP     |     3 |    66 |     2   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | E_SORT1 |     3 |       |     1   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   2 - access("JOB"='CLERK')


--------------------------------------------------------------------------------------------
| Id  | Operation                    | Name        | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |             |     3 |    66 |     3  (34)| 00:00:01 |
|   1 |  SORT ORDER BY               |             |     3 |    66 |     3  (34)| 00:00:01 |
|   2 |   TABLE ACCESS BY INDEX ROWID| EMP         |     3 |    66 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | E_LOW_SORT1 |     3 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   3 - access(LOWER("JOB")='clerk')

The redundant SORT ORDER BY is present in 10g even for a simple index range scan. By 11.2.0.4 the optimizer was able to get rid of the redundant step, but clearly there’s a little gap in the code relating to the over() clause that hasn’t acquired the correction – even in 18.3.0.0 (or 19.2 according to a test on https://livesql.oracle.com).

To fix the 10g problem you just had to include the first column of the index in the order by clause: the result doesn’t change, of course, because you’re simply prefixing the required columns with a column which holds the single value you were probing the index for but suddenly the optimizer realises that it can do a NOSORT operation – so the “obvious” guess was to do the same for this “first fetch” example:

select * from emp where lower(job)='clerk' order by lower(job), hiredate fetch first 2 rows only;

--------------------------------------------------------------------------------------------------------------------
| Id  | Operation                     | Name        | Starts | E-Rows | Cost (%CPU)| A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT              |             |      1 |        |     3 (100)|      2 |00:00:00.01 |       4 |
|*  1 |  VIEW                         |             |      1 |      2 |     3  (34)|      2 |00:00:00.01 |       4 |
|*  2 |   WINDOW NOSORT STOPKEY       |             |      1 |      1 |     3  (34)|      2 |00:00:00.01 |       4 |
|   3 |    TABLE ACCESS BY INDEX ROWID| EMP         |      1 |      1 |     2   (0)|      3 |00:00:00.01 |       4 |
|*  4 |     INDEX RANGE SCAN          | E_LOW_SORT1 |      1 |      1 |     1   (0)|      3 |00:00:00.01 |       2 |
--------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("from$_subquery$_002"."rowlimit_$$_rownumber"<=2)
   2 - filter(ROW_NUMBER() OVER ( ORDER BY "EMP"."SYS_NC00009$","EMP"."HIREDATE")<=2)
   4 - access("EMP"."SYS_NC00009$"='clerk')

It’s just one of those silly little details where you can waste a HUGE amount of time (in a complex case) because it never crossed your mind that something that clearly ought to work might need testing for a specific use case – and I’ve lost count of the number of times I’ve been caught out by this type of “not quite finished” anomaly.

Footnote

If you follow the URL to the Oracle-L thread you’ll see that Tanel Poder has supplied a couple of MoS Document Ids discussing the issue and warning of other bugs with virtual column / FBI translation, and has shown an alternative workaround that takes advantage of a hidden parameter.

 

2 Comments »

  1. One thing we found with this WINDOWS NOSORT STOPKEY optimisati in “FETCH FIRST 1 ROWS WITH TIES” and in analytic functions RANK and DENSE_RANK is that it returns wrong results for us (tested on 18.6 and 19.3) – as if ony only the first row gets fetched instead of all entries of ranke 1:

    Here is our test case, similar to our real setup:

    You need a table with index::

     create table test 
     as 
     select pos_id, done_date, seq_nr 
     from 
      (select level pos_id from dual connect by level <= 1000) 
     ,(select trunc(sysdate) - level done_date from dual connect by level <= 500) 
     ,(select level seq_nr from dual connect by level <= 2)
    ;
    
    create index test#i on test(pos_id, done_date);
    

    (A) Query using “fetch first 1 rows with ties”

    
    select /*+ INDEX(test test#i) */
            ep.seq_nr, ep.pos_id, ep.done_date
    from
            test ep
    where
            ep.pos_id = 1
    fetch first 1 rows with ties
    /
    
    

    Only 1 result instead of 2.

    (B) Wrong result of the query with analytic function RANK() which gets executed for (A):

     select * from ( 
     select /*+ INDEX(test test#i) */ 
     ep.seq_nr, ep.pos_id, ep.done_date 
     ,rank() over (order by pos_id, done_date) rk 
     from test ep 
     where ep.pos_id = 1 
     ) where rk  <= 1
    /
    

    Only 1 result instead of 2.

    Similar for DENSE_RANK().
    ROW_NUMBER() is not concerned, “obviously”.

    Execution plan contains in both cases WINDOW NOSORT *STOPKEY*

     ---------------------------------------------------------------------------------------- 
     |  Id | Operation                      | Name   | Rows | Bytes | Cost (%CPU)| Time     | 
     ---------------------------------------------------------------------------------------- 
     |   0 | SELECT STATEMENT               |        |    1 |    48 |       2 (0)| 00:00:01 | 
     | * 1 |  VIEW                          |        |    1 |    48 |       2 (0)| 00:00:01 | 
     | * 2 |   WINDOW NOSORT STOPKEY        |        |    1 |    35 |       2 (0)| 00:00:01 | 
     |   3 |    TABLE ACCESS BY INDEX ROWID | TEST   |    1 |    35 |       2 (0)| 00:00:01 | 
     | * 4 |     INDEX RANGE SCAN           | TEST#I |  917 |       |       2 (0)| 00:00:01 | 
     ---------------------------------------------------------------------------------------
    - 
    

    In case (B), there are two results with RK = 1 returned if the condition “where rk <= 1" is removed.

    Of course, this removal leads to a different execution plan with only "WINDOW NOSORT" and with a much longer, suboptimal, INDEX RANGE SCAN and more table accesses.

    (W) Our workaround (also with a less efficient execution plan) is:

    select * from ( 
     select /*+ INDEX(test test#i) */ 
     ep.seq_nr, ep.pos_id, ep.done_date 
     ,rank() over (partition by pos_id order by done_date) rk 
     from test ep 
     where ep.pos_id = 1 
    order by ep.pos_id, ep.done_date 
    ) where rk <= 1 
     ; 
    

    Probably because the Optimizer does not recognize the potential for rewrite and thus does not optimize the query to WINDOW NOSORT STOPKEY.

    We have opened a Service Request on the topic:
    "SR 3-20865153311 : Wrong results in SQL with WINDOW NOSORT STOPKEY optimizations in execution plan"

    Our real-life table on which the problem occurs looks similar, but larger and partitioned by a column which correlates rougly with trunc(done_date,'MM'). so that the difference of execution plans with WINDOW NOSORT STOPKEY and WINDOW NOSORT execution plans is significant and visible in their execution times and resource usage. Something like

     create table test 
     as 
     select pos_id, done_date, seq_nr 
     from 
      (select level pos_id from dual connect by level <= 100000) 
     ,(select trunc(sysdate) - level done_date from dual connect by level <= 300 * 8) 
     ,(select level seq_nr from dual connect by level <= 100) 
    order by done_date, seq_nr, pos_id
    ;
    
    

    Comment by Thomas Mautsch — August 30, 2019 @ 6:20 am BST Aug 30,2019 | Reply

    • Thomas,

      Thanks for supplying the exampl and the MoS reference.
      Unfortunately WordPress made a bit of a mess of the formatting and lost some text thanks to its problems with the “less than” sign.
      I’ve cleaned up as best I can, but there’s obviously some missing text. If it’s just in the opening Create Table statement you could email me the text and I’ll edit it back in, or add it as comment that I’ll cut and paste.

      To work around formatting issues there are two steps:
      1) add the tags “sourcecode” “/sourcecode” using square brackets instead of quote marks around the text
      2) if you include a “less than” sign then add the text “– > comment” as the end of the line. This(usually) stops wordpress reading everything between a “less than” and the next “greater than” as a format tag. (alternatively use “.le.” for “less than or equal to” and “.lt.” for “less than”

      Regards
      Jonathan Lewis

      Comment by Jonathan Lewis — August 30, 2019 @ 2:40 pm BST Aug 30,2019 | Reply


RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

WordPress.com Logo

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

Google photo

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

Twitter picture

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

Facebook photo

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

Connecting to %s

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

Powered by WordPress.com.