«

»

Jan
18

Min/Max Optimization

I recently rediscovered an interesting case when performance took a major hit after a tiny change (or so I thought).

Oracle implements a special min/max optimization that is used to spead up queries where we just want the MIN or MAX value of an indexed column. This optimization is only available when querying the leading column of an index.

The original query looked like this:

SELECT MAX(a) 
  FROM numbers;
-------------------------------------------------
| Id  | Operation                  | Name       |
-------------------------------------------------
|   0 | SELECT STATEMENT           |            |
|   1 |  SORT AGGREGATE            |            |
|   2 |   INDEX FULL SCAN (MIN/MAX)| NUMBERS_A  |
-------------------------------------------------

The query clocked in at a few seconds for a table with several hundred million rows. I also wanted the minimum value, so I modified the query like this:

SELECT MAX(a)
      ,MIN(a)
  FROM numbers;

This time, the query kept running for several minutes before I remembered something. I pulled out the execution plan, and saw that the optimizer had changed from the min/max scan to a regular index ffs. The actual index was a large composite index of a few gigabytes…

--------------------------------------------
| Id  | Operation             | Name       |
--------------------------------------------
|   0 | SELECT STATEMENT      |            |
|   1 |  SORT AGGREGATE       |            |
|   2 |   INDEX FAST FULL SCAN| NUMBERS_A  |
--------------------------------------------

The min/max optimization can be used for queries where either MIN(col) or MAX(col) (but not both) are present as the only item in the select list. When I added the MIN(), I hit one of the restrictions, so the only available access path was an INDEX FFS. In order to use the optimization for both MIN() and MAX() I had to rewrite the query.

Here are two forms of the same query, that makes use if the min/max optimization. The first one with cross join of two derived tables, the second one with a query against dual with two scalar sub queries.

SELECT *
  FROM (SELECT MAX(a) FROM numbers)
      ,(SELECT MIN(a) FROM numbers);
---------------------------------------------------
| Id  | Operation                    | Name       |
---------------------------------------------------
|   0 | SELECT STATEMENT             |            |
|   1 |  NESTED LOOPS                |            |
|   2 |   VIEW                       |            |
|   3 |    SORT AGGREGATE            |            |
|   4 |     INDEX FULL SCAN (MIN/MAX)| NUMBERS_A  |
|   5 |   VIEW                       |            |
|   6 |    SORT AGGREGATE            |            |
|   7 |     INDEX FULL SCAN (MIN/MAX)| NUMBERS_A  |
---------------------------------------------------
SELECT (SELECT MAX(a) FROM numbers)
      ,(SELECT MIN(a) FROM numbers)
  FROM dual;
-------------------------------------------------
| Id  | Operation                  | Name       |
-------------------------------------------------
|   0 | SELECT STATEMENT           |            |
|   1 |  SORT AGGREGATE            |            |
|   2 |   INDEX FULL SCAN (MIN/MAX)| NUMBERS_A  |
|   3 |  SORT AGGREGATE            |            |
|   4 |   INDEX FULL SCAN (MIN/MAX)| NUMBERS_A  |
|   5 |  FAST DUAL                 |            |
-------------------------------------------------

Leave a Reply

Your email address will not be published.

You may use these HTML tags and attributes: <a href="" title=""> <abbr title=""> <acronym title=""> <b> <blockquote cite=""> <cite> <code> <del datetime=""> <em> <i> <q cite=""> <strike> <strong> <pre lang="" line="" escaped="">