Skip to Main Content
  • Questions
  • Suboptimal execution plan for filter + order by

Breadcrumb

Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Connor McDonald

Thanks for the question, Eyal.

Asked: December 24, 2015 - 12:23 pm UTC

Answered by: Connor McDonald - Last updated: January 01, 2016 - 4:49 am UTC

Category: Database - Version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production

Viewed 1000+ times

You Asked

I have the following log table with millions of records and the following index:

CREATE TABLE log_data( 
  account_id NUMBER, 
  log_type NUMBER, 
  sys_name VARCHAR2(30), 
  log_time TIMESTAMP,
  msg CLOB
  );
create index log_date on log_data (account_id, log_type, NLSSORT(sys_name,'NLS_SORT=BINARY_CI'), log_time);


We're working with linguistic comparisons and case insensitive sort like this:
ALTER session SET nls_comp=linguistic;
ALTER session SET nls_sort=binary_ci;


I would like to get the latest 10 log events of a certain type for a specific system in a specific account:
explain plan for 
SELECT  *
FROM
  (
    SELECT  log_time,  msg
    FROM log_data
    WHERE
      account_id=5
      AND log_type=2
      AND sys_name='system1'
    ORDER BY
      log_time DESC
  )
WHERE
  rownum<10;

SELECT * FROM table(DBMS_XPLAN.DISPLAY);

This shows:
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                
| Id  | Operation                       | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                
|   0 | SELECT STATEMENT                |          |     9 | 18135 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                
|*  1 |  COUNT STOPKEY                  |          |       |       |            |          |                                                                                                                                                                                                                
|   2 |   VIEW                          |          |   104 |   204K|     3   (0)| 00:00:01 |                                                                                                                                                                                                                
|*  3 |    SORT ORDER BY STOPKEY        |          |   104 |   220K|     3   (0)| 00:00:01 |                                                                                                                                                                                                                
|   4 |     TABLE ACCESS BY INDEX ROWID | LOG_DATA |   104 |   220K|     3   (0)| 00:00:01 |                                                                                                                                                                                                                
|*  5 |      INDEX RANGE SCAN DESCENDING| LOG_DATE |     1 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                                
--------------------------------------------------------------------------------------------                                                                                                                                                                                                                
                                                                                                                                                                                                                                                                                                            
   1 - filter(ROWNUM<10)                                                                                                                                                                                                                                                                                    
   3 - filter(ROWNUM<10)                                                                                                                                                                                                                                                                                    
   5 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND                                                                                                                                                                                                                                                           
              NLSSORT("SYS_NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('73797374656D3100') )                                                                                                                                                                                                                   


Note on line 3 there is a sort operation which I'd like to avoid because this table contains millions of records.

The interesting thing is that this seems to be related to the linguistic comparison because if I change my session to use binary and change the index as follows, the plan looks good:

ALTER session SET nls_comp=binary;
create index log_date on log_data (account_id, log_type, sys_name, log_time);

explain plan for 
SELECT  *
FROM
  (
    SELECT  log_time,  msg
    FROM log_data
    WHERE
      account_id=5
      AND log_type=2
      AND sys_name='system1'
    ORDER BY
      log_time DESC
  )
WHERE
  rownum<10;

SELECT * FROM table(DBMS_XPLAN.DISPLAY);


shows this:

-------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |                                                                                                                                                                                                                 
-------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
|   0 | SELECT STATEMENT               |          |     9 | 18135 |     3   (0)| 00:00:01 |                                                                                                                                                                                                                 
|*  1 |  COUNT STOPKEY                 |          |       |       |            |          |                                                                                                                                                                                                                 
|   2 |   VIEW                         |          | 10442 |    20M|     3   (0)| 00:00:01 |                                                                                                                                                                                                                 
|   3 |    TABLE ACCESS BY INDEX ROWID | LOG_DATA | 10442 |    20M|     3   (0)| 00:00:01 |                                                                                                                                                                                                                 
|*  4 |     INDEX RANGE SCAN DESCENDING| LOG_DATE |     9 |       |     2   (0)| 00:00:01 |                                                                                                                                                                                                                 
-------------------------------------------------------------------------------------------                                                                                                                                                                                                                 
                                                                                                                                                                                                                                                                                                            
Predicate Information (identified by operation id):                                                                                                                                                                                                                                                         
---------------------------------------------------                                                                                                                                                                                                                                                         
                                                                                                                                                                                                                                                                                                            
   1 - filter(ROWNUM<10)                                                                                                                                                                                                                                                                                    
   4 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "SYS_NAME"='system1')                                                                                                                                                                                                                                     


Beautiful! no sort.

What am I missing? Is it possible to avoid the SORT when using a linguistic comparison?

and we said...

SORT BY STOPKEY shouldnt such a large issue in this case. For example,

SQL> CREATE TABLE log_data(
  2    account_id NUMBER,
  3    log_type NUMBER,
  4    sys_name VARCHAR2(30),
  5    log_time TIMESTAMP,
  6    msg VARCHAR2(30)
  7    );

Table created.

SQL>
SQL> insert /*+ APPEND */ into log_Data
  2  select trunc(dbms_Random.value(1,1000)), mod(rownum,5), 'blah', sysdate - 300 + rownum / 1000, 'x'
  3  from dual
  4  connect by level <= 1000000;

1000000 rows created.

SQL>
SQL> create index log_date on log_data (account_id, log_type,
  2  NLSSORT(sys_name,'NLS_SORT=BINARY_CI'), log_time);

Index created.

SQL>
SQL> ALTER session SET nls_comp=linguistic;

Session altered.

SQL>
SQL> ALTER session SET nls_sort=binary_ci;

Session altered.

SQL>
SQL>
SQL> explain plan for
  2  SELECT  *
  3  FROM
  4    (
  5      SELECT  log_time,  msg
  6      FROM log_data
  7      WHERE
  8        account_id=5
  9        AND log_type=2
 10        AND sys_name='blah'
 11      ORDER BY
 12        log_time DESC
 13    )
 14  WHERE
 15    rownum<10;

Explained.

SQL>
SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------
Plan hash value: 2164520535

---------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |     1 |    30 |     4   (0)| 00:00:01 |
|*  1 |  COUNT STOPKEY                         |          |       |       |            |          |
|   2 |   VIEW                                 |          |     1 |    30 |     4   (0)| 00:00:01 |
|*  3 |    SORT ORDER BY STOPKEY               |          |     1 |    25 |     4   (0)| 00:00:01 |
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| LOG_DATA |     1 |    25 |     4   (0)| 00:00:01 |
|*  5 |      INDEX RANGE SCAN DESCENDING       | LOG_DATE |     1 |       |     3   (0)| 00:00:01 |
---------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   3 - filter(ROWNUM<10)
   5 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND
              NLSSORT("SYS_NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('626C616800'))

20 rows selected.

SQL>
SQL> set autotrace traceonly stat
SQL>
SQL> SELECT  *
  2  FROM
  3    (
  4      SELECT  log_time,  msg
  5      FROM log_data
  6      WHERE
  7        account_id=5
  8        AND log_type=2
  9        AND sys_name='blah'
 10      ORDER BY
 11        log_time DESC
 12    )
 13  WHERE
 14    rownum<10;

9 rows selected.


Statistics
----------------------------------------------------------
          0  recursive calls
          0  db block gets
        195  consistent gets
          0  physical reads
          0  redo size
        766  bytes sent via SQL*Net to client
        552  bytes received via SQL*Net from client
          2  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          9  rows processed

SQL>
SQL> set autotrace off
SQL>
SQL> SELECT /*+ gather_plan_statistics */ *
  2  FROM
  3    (
  4      SELECT  log_time,  msg
  5      FROM log_data
  6      WHERE
  7        account_id=5
  8        AND log_type=2
  9        AND sys_name='blah'
 10      ORDER BY
 11        log_time DESC
 12    )
 13  WHERE
 14    rownum<10;

LOG_TIME                                                                    MSG
--------------------------------------------------------------------------- ------------------------------
19-NOV-17 04.55.17.000000 AM                                                x
19-NOV-17 04.12.05.000000 AM                                                x
15-NOV-17 10.12.05.000000 AM                                                x
09-NOV-17 11.24.05.000000 PM                                                x
06-NOV-17 03.57.41.000000 PM                                                x
03-NOV-17 12.57.41.000000 PM                                                x
01-NOV-17 12.14.29.000000 PM                                                x
30-OCT-17 12.50.29.000000 AM                                                x
08-OCT-17 10.40.53.000000 AM                                                x

9 rows selected.


SQL> select * from table(dbms_xplan.display_cursor(null,null,'ALLSTATS LAST'))

PLAN_TABLE_OUTPUT
----------------------------------------------------------
SQL_ID  3hub5whtuc5d4, child number 0
-------------------------------------
SELECT /*+ gather_plan_statistics */ * FROM   (     SELECT  log_time,
msg     FROM log_data     WHERE       account_id=5       AND log_type=2
      AND sys_name='blah'     ORDER BY       log_time DESC   ) WHERE
rownum<10

Plan hash value: 2164520535

---------------------------------------------------------------------------------------------------------------------
| Id  | Operation                              | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |
---------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                       |          |      1 |        |      9 |00:00:00.01 |  195 |          |
|*  1 |  COUNT STOPKEY                         |          |      1 |        |      9 |00:00:00.01 |  195 |          |
|   2 |   VIEW                                 |          |      1 |      1 |      9 |00:00:00.01 |  195 |          |
|*  3 |    SORT ORDER BY STOPKEY               |          |      1 |      1 |      9 |00:00:00.01 |  195 |  2048 |  2
|   4 |     TABLE ACCESS BY INDEX ROWID BATCHED| LOG_DATA |      1 |      1 |    202 |00:00:00.01 |  195 |          |
|*  5 |      INDEX RANGE SCAN DESCENDING       | LOG_DATE |      1 |      1 |    202 |00:00:00.01 |    3 |          |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter(ROWNUM<10)
   3 - filter(ROWNUM<10)
   5 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "LOG_DATA"."SYS_NC00006$"=HEXTORAW('626C616800'))


27 rows selected.



We managed to descend the index, and we only sorted the rows AFTER the filter.

and you rated our response

  (10 ratings)

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

Reviews

That didn't answer the question...

December 25, 2015 - 6:36 am UTC

Reviewer: Eyal

Thanks for the answer Connor, but in my case, since the data is skewed, after the filter there are still millions of records and the sort operation still takes time.
We could continue debating on whether SORT BY STOPKEY is bad or not, but my original question is HOW I can avoid it in my case, and WHY is Oracle behaving differently when the comparison is linguistic vs binary.

Connor McDonald

Followup  

December 28, 2015 - 11:56 pm UTC

If you still have millions of rows after the filter, then its not a sorting problem. Because that would mean you walked through an index for millions of rows - thats a bad idea.

So I'm dubious that you actually had millions *after* the filter.

To Eyal | Sort order by Stop key

December 28, 2015 - 8:37 am UTC

Reviewer: Rajeshwaran, Jeyabal

after the filter there are still millions of records and the sort operation still takes time

Eyal, Connor say's this "SORT BY STOPKEY shouldnt such a large issue in this case." because, Oracle doesn't sort the entire set, it just skips sorting when it find ten rows. (it is an optimization)

so sorting ten rows shouldn't take much time.

How about this? could you post either the Tkprof or gather_plan_statistics hint with dbms_xplan.display_cursor output for your query here?

that helps to sort out the time spend in "Sort order by Stop key" step.
Connor McDonald

Followup  

December 28, 2015 - 11:57 pm UTC

Agreed,

Ok, but I'm still curious

December 29, 2015 - 7:00 am UTC

Reviewer: Eyal

I'm still curious as to why the execution plan is different when comparing the string with binary vs linguistic.

You need to change DDL of index and ORDER BY

December 29, 2015 - 8:16 am UTC

Reviewer: Mikhail Velikikh from Novosibirsk, Russia

Hi Eyal,

I understand your points and requirements.
To be able to eliminate the "SORT ORDER BY STOPKEY" step you need 2 things:
1. include LOG_TIME DESC in index
2. change order by to: ORDER BY sys_name, log_time desc

Below is a short demonstration. I used Oracle Database 12.1.0.2.10 (Database Bundle Patch for Engineering Systems and DB In-Memory).

</>
SQL> drop table log_data;
SQL>
SQL> CREATE TABLE log_data(
2 account_id NUMBER,
3 log_type NUMBER,
4 sys_name VARCHAR2(30),
5 log_time TIMESTAMP,
6 msg VARCHAR2(30)
7 );
SQL>
SQL> insert /*+ APPEND */ into log_Data
2 select trunc(dbms_Random.value(1,1000)), mod(rownum,5), 'blah', sysdate - 300 + rownum / 1000, 'x'
3 from dual
4 connect by level <= 1000000;
SQL>
SQL> commit;
SQL>
SQL> create index log_date on log_data (account_id, log_type, NLSSORT(sys_name,'NLS_SORT=BINARY_CI'), log_time desc);
SQL>
SQL> ALTER session SET nls_comp=linguistic;
SQL>
SQL> ALTER session SET nls_sort=binary_ci;
SQL>
SQL> explain plan for
2 select /*+ gather_plan_statistics */
3 *
4 FROM
5 (
6 SELECT log_time, msg
7 FROM log_data
8 WHERE
9 account_id=5
10 AND log_type=2
11 AND sys_name='blah'
12 ORDER BY sys_name, log_time desc
13 )
14 WHERE
15 rownum<10;
SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 2537282416

------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 30 | 5 (20)| 00:00:01 |
|* 1 | COUNT STOPKEY | | | | | |
| 2 | VIEW | | 1 | 30 | 5 (20)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID| LOG_DATA | 1 | 25 | 4 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | LOG_DATE | 1 | | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
4 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND
NLSSORT("SYS_NAME",'nls_sort=''BINARY_CI''')=HEXTORAW('626C616800'))
SQL> set serverout off
SQL> select /*+ gather_plan_statistics */
2 *
3 FROM
4 (
5 SELECT log_time, msg
6 FROM log_data
7 WHERE
8 account_id=5
9 AND log_type=2
10 AND sys_name='blah'
11 ORDER BY sys_name, log_time DESC
12 )
13 WHERE
14 rownum<10
15 ;

LOG_TIME MSG
--------------------------------------------------------------------------- ------------------------------------------------------------------------------------------
24.11.2017 19:57:43.000000 x
18.11.2017 15:16:55.000000 x
17.11.2017 11:33:43.000000 x
16.11.2017 07:00:07.000000 x
11.11.2017 12:45:43.000000 x
08.11.2017 11:33:43.000000 x
31.10.2017 12:45:43.000000 x
30.10.2017 14:40:55.000000 x
22.10.2017 13:21:43.000000 x
SQL> select * from table(dbms_xplan.display_cursor(format=>'allstats last'));

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID 89vqw25ghyx78, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * FROM ( SELECT
log_time, msg FROM log_data WHERE account_id=5 AND
log_type=2 AND sys_name='blah' ORDER BY sys_name, log_time
DESC ) WHERE rownum<10

Plan hash value: 2537282416

-------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | Reads |
-------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 9 |00:00:00.01 | 13 | 78 |
|* 1 | COUNT STOPKEY | | 1 | | 9 |00:00:00.01 | 13 | 78 |
| 2 | VIEW | | 1 | 1 | 9 |00:00:00.01 | 13 | 78 |
| 3 | TABLE ACCESS BY INDEX ROWID| LOG_DATA | 1 | 1 | 9 |00:00:00.01 | 13 | 78 |
|* 4 | INDEX RANGE SCAN | LOG_DATE | 1 | 1 | 9 |00:00:00.01 | 4 | 16 |
-------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

1 - filter(ROWNUM<10)
4 - access("ACCOUNT_ID"=5 AND "LOG_TYPE"=2 AND "LOG_DATA"."SYS_NC00006$"=HEXTORAW('626C616800'))
</>

Best regards,
Mikhail.

Beautiful, Mikhail!

December 29, 2015 - 8:53 am UTC

Reviewer: Eyal

Thanks for the answer Mikhail! This is exactly what I was looking for!

Since I love to understand the internals of how the optimizer works, I still find it interesting why it behaved differently between binary and linguistic.
Do you have an explanation for that?

Explanation (nearly).

December 30, 2015 - 12:03 pm UTC

Reviewer: Jonathan Lewis from UK

To address the question why does linguistic behave differently from binary - it's because the optimizer can't see that you've met the necessary requirements to avoid the sort: but I'm not sure if this is a side effect of the current implementation, or a necessary limitation (I suspect the former).

For a multi-column (say N + M columns) index you can get an order by without sorting if you have equality predicates on the first N columns and then order by the last M columns, with the restriction that ALL the M columns must have their original ASC/DESC qualifiers, or they must ALL have the opposite of their orginal ASC/DESC qualifiers.

The column for sys_name in the index is translated into an expression, so your predicate sys_name = 'system1' is NOT recognised as an equality predicate, so you have to have both the sys_name and the log_date in the order by clause. But if you want to add DESC to the log_date you either need that in the index definition (as per Mikhail's suggestion) or you need to use:

order by sys_name desc, log_date desc


As a dirty alternative (which I wouldn't advise and probably wouldn't be realistic in any case) you could find out the internal column name for the nls_sort column (in my case it was SYS_NC000006$) and use a predicate like:

and sys_nc00006$ = hextoraw('73797374656D3100')



Ideally, of course, we might hope that the optimizer would generate this predicate (rather then the one it does) and notice that this means the query matches the ordering requirement.


Connor McDonald

Followup  

January 01, 2016 - 4:45 am UTC

Thanks for stopping by Jonathan

Another strategy

December 30, 2015 - 2:26 pm UTC

Reviewer: Jonathan Lewis from UK

Rather than creating an index on the NLSSORT() value, create a virtual column and then query with an NLSSORT() value.#


alter table log_data add (
        ci_sys_name     generated always as (nlssort(sys_name, 'NLS_SORT=BINARY_CI')) virtual
)
;

-- gather stats on the hidden column

create index log_date on log_data(
        account_id,
        log_type,
        ci_sys_name,
        log_time
)
;



Here's the adjusted query with execution plan - note how we can use the user input and wrap it with the nlssort() function.

SELECT
        *
FROM
  (
    SELECT
        log_time,  msg
    FROM log_data
    WHERE
      account_id=5
      AND log_type=2
      AND ci_sys_name = nlssort('system1','NLS_SORT=BINARY_CI')
    ORDER BY
      log_time  desc   
  )
WHERE
  rownum<10;




And here's the body of the execution plan (with my data set) - showing the early elimination:


-------------------------------------------------------------------------------------------
| Id  | Operation                      | Name     | Starts | E-Rows | Cost (%CPU)| A-Rows |   
-------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT               |          |      1 |        |    12 (100)|      9 |
|*  1 |  COUNT STOPKEY                 |          |      1 |        |            |      9 |
|   2 |   VIEW                         |          |      1 |     10 |    12   (0)|      9 |
|   3 |    TABLE ACCESS BY INDEX ROWID | LOG_DATA |      1 |   1000 |    12   (0)|      9 |
|*  4 |     INDEX RANGE SCAN DESCENDING| LOG_DATE |      1 |     10 |     2   (0)|      9 |
-------------------------------------------------------------------------------------------


The TABLE E-rows shows the number of rows matching the equality predicate while the index and table A-ROWS between them show that only 9 table rows were actually accessed.


Regards
Jonathan Lewis
https://jonathanlewis.wordpress.com


Thanks for the detailed explanation

December 30, 2015 - 10:03 pm UTC

Reviewer: Eyal

Thanks, Jonathan, for the concise explanation.
I, too, suspect this is a limitation of the current implementation. I also noticed different behaviors when using different operators.

For example, keeping my original index (all defined as ASC) i would assume that sorting on both sys_name and log_date DESC would also avoid the sort, but:

SELECT  *
FROM
  (
    SELECT  log_time,  msg
    FROM log_data
    WHERE
      account_id=5
      AND log_type=2
      AND sys_name='system1' 
    ORDER BY
      sys_name desc,
      log_time DESC
  )
WHERE
  rownum<10;

-------------------------------------------------------------------------------------------  
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     |  
-------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT               |          |     9 | 18135 |     4  (25)| 00:00:01 |  
|*  1 |  COUNT STOPKEY                 |          |       |       |            |          |  
|   2 |   VIEW                         |          |    88 |   173K|     4  (25)| 00:00:01 |  
|*  3 |    SORT ORDER BY STOPKEY       |          |    88 |   186K|     4  (25)| 00:00:01 |  
|   4 |     TABLE ACCESS BY INDEX ROWID| LOG_DATA |    88 |   186K|     3   (0)| 00:00:01 |  
|*  5 |      INDEX RANGE SCAN          | LOG_DATE |     1 |       |     2   (0)| 00:00:01 |  
------------------------------------------------------------------------------------------- 


but.. surprisingly, the following trick did the job (Notice the >= and <= with sys_name)
SELECT  *
FROM
  (
    SELECT  log_time,  msg
    FROM log_data
    WHERE
      account_id=5
      AND log_type=2
      AND sys_name>='system1' AND sys_name<='system1' 
    ORDER BY
      sys_name desc,
      log_time DESC
  )
WHERE
  rownum<10;

------------------------------------------------------------------------------------------- 
| Id  | Operation                      | Name     | Rows  | Bytes | Cost (%CPU)| Time     | 
------------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT               |          |     9 | 18135 |     3   (0)| 00:00:01 | 
|*  1 |  COUNT STOPKEY                 |          |       |       |            |          | 
|   2 |   VIEW                         |          |    88 |   173K|     3   (0)| 00:00:01 | 
|   3 |    TABLE ACCESS BY INDEX ROWID | LOG_DATA |    88 |   186K|     3   (0)| 00:00:01 | 
|*  4 |     INDEX RANGE SCAN DESCENDING| LOG_DATE |     1 |       |     2   (0)| 00:00:01 | 
------------------------------------------------------------------------------------------- 


Anyway, thanks for the detailed explanation and the additional suggestions how to solve this.

Connor McDonald

Followup  

January 01, 2016 - 4:49 am UTC

That certainly is counter intuitive. If you're keen, you could log a support call for the optimizer group.

Buggy stuff.

December 31, 2015 - 7:39 am UTC

Reviewer: Jonathan Lewis from UK

Thanks for that last item.

The use of DESC with both columns will give you what you want with an index_desc() hint. My first test case did a full tablescan unhinted because of the stats, so I put in an index_desc() hint assuming that a large data set would prefer the index. I've changed the test and got an index ascending (without early elimination, and with a sort) when the tablescan got too expensive.

The second test is wierd - it gets to the same final predicates as one of the working examples, but does the right thing with early elimination. It's not immediately obvious from the 10053 traces why the different methods have different effects. They seem to start by recognising that sort elimination is possible, and them do something strange when working out the details.

You do need to check the predicate section and use the actual run-time statistics in these cases, the basic body shape of the plan can't be trusted.

Regards
Jonathan Lewis


Of course!

December 31, 2015 - 6:43 pm UTC

Reviewer: Eyal

More to Explore

DBMS_RANDOM

More on PL/SQL routine DBMS_RANDOM here