MIN/MAX and Index Full Scans

We got a question at work today from a blogger in regard to his blog post entitled MIN and MAX Functions in a Single Query are Disastrous. If you check out his post, you'll see the details of his tests. Basically, he was wondering why the optimizer would choose to do a full table scan instead of using an INDEX FULL SCAN (MIN/MAX) operation for a query that contained both a MIN and a MAX function call on the same indexed column. I thought my answer to his question to us, about why the optimizer makes such a "bad" decision about the execution plan, would be a good topic for my post today.

Normally, if you write a query that uses only one call, either MIN or MAX, the optimizer will use the INDEX FULL SCAN (MIN/MAX) optimization to read only through to the first leaf block (for MIN) and to the last leaf block (for MAX). This is a great optimization that can save hundreds/thousands of block accesses over either a full table scan or even an INDEX FAST FULL SCAN. But, as Momen's tests show, if you execute a query that has both MIN and MAX in it, the optimizer refuses to use this optimization although it would seem to us (the feeble human brain) that the optimizer should know that it could do the operation twice, once to get the first block and once to get the last block, right?

But, the optimizer can't do this. The INDEX FULL SCAN (MIN/MAX) operation will do either an index scan for the first leaf block or an index scan for the last leaf block. It can not do both at the same time. Think about it... The code to handle the operation would have to have some fairly specific logic in it to handle the dual calls.

The question to then ask is why wouldn't Oracle choose to add that logic? Well, since there's actually an easy way to get the result you want using the current operation's code path, I'd say Oracle didn't want to invest development time into writing specific case code when the simple branch of MIN or MAX can work properly as long as the developer knows how to write their SQL to invoke it.

Here's what has to happen.
This query:

select min(empno) min_empno, max(empno) max_empno
from emp;

---------------------------------------------------------------------------------
| Id  | Operation             | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------
|   0 | SELECT STATEMENT      |         |     1 |     5 |   442   (5)| 00:00:06 |
|   1 |  SORT AGGREGATE       |         |     1 |     5 |            |          |
|   2 |   INDEX FAST FULL SCAN| PK_EMP  |   917K|  4480K|   442   (5)| 00:00:06 |
---------------------------------------------------------------------------------


Statistics
----------------------------------------------------------
      196  recursive calls
        0  db block gets
     1959  consistent gets
        1  physical reads
        0  redo size
      476  bytes sent via SQL*Net to client
      381  bytes received via SQL*Net from client
        2  SQL*Net roundtrips to/from client
        6  sorts (memory)
        0  sorts (disk)
        1  rows processed

becomes this:

select a.min_empno, b.max_empno
from
  (select min(empno) min_empno from emp) a,
  (select max(empno) max_empno from emp) b;

----------------------------------------------------------------------------------------
| Id  | Operation                    | Name    | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |         |     1 |    26 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS                |         |     1 |    26 |     6   (0)| 00:00:01 |
|   2 |   VIEW                       |         |     1 |    13 |     3   (0)| 00:00:01 |
|   3 |    SORT AGGREGATE            |         |     1 |     5 |            |          |
|   4 |     INDEX FULL SCAN (MIN/MAX)| PK_EMP  |   917K|  4480K|     3   (0)| 00:00:01 |
|   5 |   VIEW                       |         |     1 |    13 |     3   (0)| 00:00:01 |
|   6 |    SORT AGGREGATE            |         |     1 |     5 |            |          |
|   7 |     INDEX FULL SCAN (MIN/MAX)| PK_EMP  |   917K|  4480K|     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------


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

The same results could also be achieved by doing a UNION of the two separate statements. Either way, what is required is that the developer must understand how the optimizer utilizes the INDEX FULL SCAN (MIN/MAX) optimization and write their SQL to take full advantage of it.

Perhaps at some point (I haven't tested this in 11g yet to see if the optimizer behaves any differently) the optimizer will have code path to handle both calls simultaneously, but for now, the developer must provide the optimizer with syntax to allow the optimizer to formulate the optimal overall execution plan.

To me, this is just one more really good example why the database should not be "just a black box" to developers. Writing high performance SQL requires understanding how the Oracle optimizer works, not just an ability to put together a statement that gets the correct answer.

Thanks to Momen for his post and his question to Method R on this. It was good food for thought and nice blog fodder! :)

Addendum - July 11, 2008, 8:57am
Thanks to my colleague Ron who did the 11g test on this and he confirmed that the optimizer behavior has not changed. So, even in 11g, the SQL would need to be written to submit the MIN and MAX calls separately in order to get the optimal execution plan.