Skip to Main Content
  • Questions
  • Why is the optimizer not choosing INDEX FULL/RANGE SCAN (MIN/MAX) with KEEP function

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Zilvinas.

Asked: May 24, 2017 - 2:02 pm UTC

Last updated: July 11, 2017 - 3:18 pm UTC

Version: Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production

Viewed 1000+ times

You Asked

Hello,

I have a function MAX(b) KEEP(DENSE_RANK LAST ORDER BY a) and want to get execution plan like this:
----------------------------------------------------
| Id  | Operation                   | Name | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |
|   1 |  TABLE ACCESS BY USER ROWID | T    |     1 |
|   2 |   SORT AGGREGATE            |      |     1 |
|   4 |    INDEX FULL SCAN (MIN/MAX)| PK_T |     1 |
----------------------------------------------------


There script to play with:
CREATE TABLE t(a NUMBER CONSTRAINT PK_T PRIMARY KEY, b NUMBER)
/
EXPLAIN PLAN FOR
  SELECT MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
DROP TABLE t PURGE
/


As you can see in first query optimizer in not using index at all.

Second attempt is almost fine, but there is two scans of index.
It should not be necessary.

Third attempt should scan index once and then just fetch record by rowid.
But optimizer refuses to use index.
All necessary data is in the index to find last entry and return rowid.
But it is not working. Why?

I've tried with more than one column in the index.
Then optimizer chooses index range scan by predicate on first index column, but not uses INDEX RANGE SCAN (MIN/MAX)
Sript:
CREATE TABLE t(a NUMBER, b NUMBER, c NUMBER, CONSTRAINT PK_T PRIMARY KEY(a, b))
/
EXPLAIN PLAN FOR
  SELECT MAX(c) KEEP(DENSE_RANK LAST ORDER BY a, b)
    FROM t
   WHERE a = 1
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT c
    FROM t
   WHERE a = 1
     AND b = (SELECT MAX(b) FROM t WHERE a = 1)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a, b) FROM t WHERE a = 1)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/
DROP TABLE t PURGE
/


and Chris said...

In your first example, the primary key index only contains the column a. So in order to execute:

  SELECT MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t


Oracle Database has to access the table to get the values for column b. So if you use the index it has to read both the table and the index. You have no where clause so it can't do any filtering in the index itself. So it has to full scan it:

EXPLAIN PLAN FOR
  SELECT /*+ index (t PK_T) */MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/

-------------------------------------------------------------  
| Id  | Operation                            | Name | Rows  |  
-------------------------------------------------------------  
|   0 | SELECT STATEMENT                     |      |     1 |  
|   1 |  SORT AGGREGATE                      |      |     1 |  
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |     1 |  
|   3 |    INDEX FULL SCAN                   | PK_T |     1 |  
------------------------------------------------------------- 


So you've swapped a full table scan for a full index scan + table access by rowid. In most cases this will be more work, as we can see if we load the table up with data and get the execution plan. Pay attention to the buffers column:

insert into t 
with rws as (
  select level a, level b from dual connect by level <= 10000
)
  select a, b from rws;
commit;
exec dbms_stats.gather_table_stats(user, 't');


SELECT /*+ gather_plan_statistics index (t PK_T) */MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
FROM t
/
select * from table(dbms_xplan.display_cursor(null, null, 'IOSTATS LAST'));
/

-------------------------------------------------------------------------------------------------------  
| Id  | Operation                            | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
-------------------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT                     |      |      1 |        |      1 |00:00:00.01 |      37 |  
|   1 |  SORT AGGREGATE                      |      |      1 |      1 |      1 |00:00:00.01 |      37 |  
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| T    |      1 |  10000 |  10000 |00:00:00.04 |      37 |  
|   3 |    INDEX FULL SCAN                   | PK_T |      1 |  10000 |  10000 |00:00:00.01 |      19 |  
-------------------------------------------------------------------------------------------------------

SELECT /*+ gather_plan_statistics */MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
FROM t
/
select * from table(dbms_xplan.display_cursor(null, null, 'LAST BASIC IOSTATS'));
/
----------------------------------------------------------------------------------------------  
| Id  | Operation          | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  
----------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT   |      |      1 |        |      1 |00:00:00.01 |      23 |      1 |  
|   1 |  SORT AGGREGATE    |      |      1 |      1 |      1 |00:00:00.01 |      23 |      1 |  
|   2 |   TABLE ACCESS FULL| T    |      1 |  10000 |  10000 |00:00:00.01 |      23 |      1 |  
---------------------------------------------------------------------------------------------- 


Notice that the index + table access uses 37 buffers. But the full table scan accesses just 23. So it does 14 more IO operations.

In your second example:

  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)


the table t appears twice. So the database will access it twice in the plan! It has to read the index first to find the maximum value for A. Then access the table (using the index) to find the value for b.

In your third example the optimizer choosing a full table scan is likely an due to the fact your table is empty. If there's no rows it doesn't make any difference whether you scan the index or the table: there's nothing to read!

Keeping the table loaded with data from my previous test and the optimizer does choose an index full scan:

EXPLAIN PLAN FOR
  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)
/
SELECT * FROM TABLE(Dbms_Xplan.Display(format => 'basic +rows'))
/

---------------------------------------------------  
| Id  | Operation                  | Name | Rows  |  
---------------------------------------------------  
|   0 | SELECT STATEMENT           |      |     1 |  
|   1 |  TABLE ACCESS BY USER ROWID| T    |     1 |  
|   2 |   SORT AGGREGATE           |      |     1 |  
|   3 |    INDEX FAST FULL SCAN    | PK_T | 10000 |  
--------------------------------------------------- 

SELECT /*+ gather_plan_statistics */b
FROM t
WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t);

select * from table(dbms_xplan.display_cursor(null, null, 'LAST BASIC IOSTATS'));

---------------------------------------------------------------------------------------------  
| Id  | Operation                  | Name | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  
---------------------------------------------------------------------------------------------  
|   0 | SELECT STATEMENT           |      |      1 |        |      1 |00:00:00.01 |      25 |  
|   1 |  TABLE ACCESS BY USER ROWID| T    |      1 |      1 |      1 |00:00:00.01 |      25 |  
|   2 |   SORT AGGREGATE           |      |      1 |      1 |      1 |00:00:00.01 |      24 |  
|   3 |    INDEX FAST FULL SCAN    | PK_T |      1 |  10000 |  10000 |00:00:00.01 |      24 |  
---------------------------------------------------------------------------------------------


You've got the same problems in your second set of queries. Yes, you've added another column to the PK. But you've also added another column to the table that's not part of the index! And you've still got no data, which is likely to lead to "usual" plans.

Also note that in general your queries aren't equivalent. The function:

MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)


Sorts the rows by A and finds the greatest value. It then inspects all the rows with this value and returns the largest value for B among them.

When you do:

  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)


You find the largest value for A, then return B for all the rows that have that value.

And with:

  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)


You find all the rows which have the largest value for A. Then return B for whichever of these has the highest rowid.

In cases where A is not unique, these can all return different results:

CREATE TABLE t(a NUMBER , b NUMBER)
/
insert into t values (1, 2);
insert into t values (1, 3);
insert into t values (1, 1);
commit;

  SELECT MAX(b) KEEP(DENSE_RANK LAST ORDER BY a)
    FROM t
/

MAX(B)KEEP(DENSE_RANKLASTORDERBYA)  
3 

  SELECT b
    FROM t
   WHERE a = (SELECT MAX(a) FROM t)
/

B  
2  
3  
1 

  SELECT b
    FROM t
   WHERE ROWID = (SELECT MAX(ROWID) KEEP(DENSE_RANK LAST ORDER BY a) FROM t)
/

B  
1  

Rating

  (1 rating)

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

Comments

Zilvinas Vidmantas, May 26, 2017 - 11:09 am UTC

I understand what is hapening.
What I do not understand why Oracle not uses index scan min/max.

SELECT MAX(b) KEEP(DENSE_RANK LAST ORDER BY a) FROM t

Yes, of course table must be accessed not only index, but only for one record.

Index is unique, so there's only one record to access table.

So best possible algorithm to execute it would be:
1. Scan index min/max, to find last entry in index.
2. Take rowid of that index entry and fetch that only record from table by.

My second example is written to force Oracle to do that and it works fine. But of course it need to access index twice. As you said sql is written this way(can't work out better sql). That is because Oracle from subquery only gets max value. So second visit to index is needed to get rowid. The goal is to take not max value using min/max scan, but rowid already with first index visit. MAX(rowid) KEEP(DENSE_RANK LAST ORDER BY a) looks like correct way to achieve this, but Oracle is not doing this. It chooses index full/range scan or even table full scan but not index min/max scan.

Goal is to get execution plan like this:
----------------------------------------------------
| Id  | Operation                   | Name | Rows  |
----------------------------------------------------
|   0 | SELECT STATEMENT            |      |     1 |
|   1 |  TABLE ACCESS BY USER ROWID | T    |     1 |
|   2 |   SORT AGGREGATE            |      |     1 |
|   3 |    INDEX FULL SCAN (MIN/MAX)| PK_T |     1 |
----------------------------------------------------

Technically there should be no problems to do this. But I can't force oracle to do this. Even rewriting query not helps. Second example is most closest what I could achieve.

I understand that MAX() KEEP() function selects max from ALL rows that has the same last value in index, but in my case index is unique. So optimizer should use this fact.
I believe in all examples there should by index min/max scans. It is technicaly possible in all queries.

What I wondering about, is does Oracle optimizer not smart enough or there's something that prevents it from choosing index scan min/max and I can rewrite or do something else.


Chris Saxon
July 11, 2017 - 3:18 pm UTC

The reason is simply that this optimization isn't implemented. The example is an edge case, so I'm not surprised...

More to Explore

Analytics

Analytic SQL got you confused? Check out Connor McDonald's complete video course.