Thursday, July 10, 2008

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".

8 comments:

Hemant K Chitale said...

Try either of the UNION or Inline View methods I've shown at

http://hemantoracledba.blogspot.com/2007/08/when-cost-doesnt-indicate-true-load.html

Anonymous said...

also change your uindex to a primary key constraint and It'll do an index fast full scan rather than a tablescan.
Not sure why it doesn't do this with a unique index.

Anonymous said...

Hi,

I would expected oracle to use a full index scan, as Karen Morten has showed on her blog.

You could try to do the query again, but this time creating the table with an additional "make_big" field (like char(100) or something)?
Probably this will cause oracle to use a full index scan (not as nice as an index full scan min/max, but normally better then a full table scan).

Bundit said...

Even if you use "select min(c1), min(c1) from table", it will always not to use fast full index scan".

Bundit said...

Even if either min or max function used on single query, but calling twice e.g. "select min(c1), min(c1) from table", it will always not to use fast full index scan.

Hemant K Chitale said...

An IndexFullScan would be used if the column is defined as NOT NULL.


A "hack" I used to create one more solution is to define a Materialized View (it can even by Refresh On Commit) to hold the MIN and MAX values in 2 columns and 1 row. Querying that MV is very fast !

Hemant K Chitale said...

If you define the column as a NOT NULL, Oracle can use an IndexFullScan (but not FastFullScan).

Asif Momen said...

Hi Hemant,

Yes, after making column as NOT NULL, Oracle Optimizer is picking "INDEX FAST FULL SCAN" instead of "TABLE ACCESS FULL".


SQL> alter table t modify (sno not null);

Table altered.

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

MIN(SNO) MAX(SNO)
---------- ----------
1 10000


Execution Plan
----------------------------------------------------------
Plan hash value: 1058879072

-------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 4 | 7 (0)| 00:00:01 |
| 1 | SORT AGGREGATE | | 1 | 4 | | |
| 2 | INDEX FAST FULL SCAN| T_IDX | 10000 | 40000 | 7 (0)| 00:00:01 |
-------------------------------------------------------------------------------

Thanks for sharing your thoughts.

Regards