MIN and MAX Functions in a Single Query are Disastrous

Dear Readers,

I would like to discuss a very interesting point about indexes in this post. When we are interested in finding out the minimum value of an indexed column, instead of reading entire table or the index, Oracle intelligently uses the index to navigate to the first index leaf block (leftmost index block) and quickly finds the minimum value of an indexed column.

A simple demo proves this:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - Prod
PL/SQL Release 10.2.0.4.0 - Production
CORE    10.2.0.4.0      Production
TNS for 32-bit Windows: Version 10.2.0.4.0 - Production
NLSRTL Version 10.2.0.4.0 - Production

SQL>
SQL> drop table t purge;

Table dropped.

SQL>
SQL>
SQL> create table t as select level sno, 'name ' || level name 
  2  from dual connect by level <= 10000000;

Table created.

SQL>

SQL> create unique index t_idx on t(sno);

Index created.

SQL> exec dbms_stats.gather_table_stats(user, 't');

PL/SQL procedure successfully completed.

SQL> set autotrace on
SQL>
SQL> select min(sno) from t;

  MIN(SNO)
----------
         1


Execution Plan
----------------------------------------------------------
Plan hash value: 2683064407

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE            |       |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |    10M|    57M|            |          |
------------------------------------------------------------------------------------


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

SQL>
SQL> set autotrace off
SQL>

Although this table contains 10 Million rows, Oracle required only 3 consistent gets to fetch the minimum value. Superb !!!

Similarly, when finding out the maximum value, Oracle reads the last block on the right-hand side of the index structure.

SQL> set autotrace on
SQL> select max(sno) from t;

  MAX(SNO)
----------
  10000000


Execution Plan
----------------------------------------------------------
Plan hash value: 2683064407

------------------------------------------------------------------------------------
| Id  | Operation                  | Name  | Rows  | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT           |       |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE            |       |     1 |     6 |            |          |
|   2 |   INDEX FULL SCAN (MIN/MAX)| T_IDX |    10M|    57M|            |          |
------------------------------------------------------------------------------------


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

SQL> set autotrace off
SQL>

Once again only 3 consistent gets were required to fetch the maximum value.

But things get messy when you use both MIN and MAX functions in the same query. Instead of using same "INDEX FULL SCAN (MIN/MAX)" path to read the left-most block and right-most block to arrive at the minimum and maximum values, Oracle goes with FULL TABLE SCAN. A Full Table Scan on 10 Million rows !!!

SQL> set autotrace on
SQL> select min(sno), max(sno) from t;

  MIN(SNO)   MAX(SNO)
---------- ----------
         1   10000000


Execution Plan
----------------------------------------------------------
Plan hash value: 2966233522

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |     6 |  9034   (2)| 00:01:49 |
|   1 |  SORT AGGREGATE    |      |     1 |     6 |            |          |
|   2 |   TABLE ACCESS FULL| T    |    10M|    57M|  9034   (2)| 00:01:49 |
---------------------------------------------------------------------------


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

SQL> set autotrace off
SQL>

From this terrible behavior of Oracle Optimizer, What I infer and suggest is:

"Write separate queries to fetch MIN and MAX values instead of combining them into one query".