Using an Index to Shortcut a MIN/MAX Aggregate – Part 2

I’ve previously commented about the Index Scan MIN/MAX optimisation. My description of this operation from that post is below:

the optimiser can take a shortcut in satisfying a MIN or MAX aggregate by plucking the first or last value from the index, thus avoiding scanning the whole segment

I’ve been meaning to revisit and define this a bit more clearly. The target is not necessarily the first or last value in the index, the optimiser can use a similar operation to pluck a value from the middle of a composite index by using an “INDEX RANGE SCAN (MIN/MAX)” rather than “INDEX (FULL SCAN (MIN/MAX))”. An example is below:

Create a test table.

CREATE TABLE tab1 AS
SELECT MOD(ROWNUM,5) group_id
, ROWNUM id
FROM dual 
CONNECT BY LEVEL <= 20000;

SELECT MIN(group_id), MAX(group_id) FROM tab1;

MIN(GROUP_ID) MAX(GROUP_ID)
------------- -------------
            0             4

CREATE INDEX tab1_i ON tab1 (group_id,id);

The index is on GROUP and ID so the query below using GROUP_ID “0” will be doing as I previously described – plucking the first value from the index (note: I executed the queries below previously to first charge the shared pool).

set autotrace on
SELECT MIN(id) FROM tab1 WHERE group_id = 0;

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    26 |            |          |
|   2 |   FIRST ROW                  |        |  4000 |   101K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| TAB1_I |  4000 |   101K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          2  consistent gets

And now we query using GROUP_ID “3”. This is going to find its target value somewhere near the middle of the index.

set autotrace on
SELECT MIN(id) FROM tab1 WHERE group_id = 3;

---------------------------------------------------------------------------------------
| Id  | Operation                    | Name   | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |        |     1 |    26 |     2   (0)| 00:00:01 |
|   1 |  SORT AGGREGATE              |        |     1 |    26 |            |          |
|   2 |   FIRST ROW                  |        |  4000 |   101K|     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN (MIN/MAX)| TAB1_I |  4000 |   101K|     2   (0)| 00:00:01 |
---------------------------------------------------------------------------------------

Statistics
----------------------------------------------------------
          2  consistent gets

2 consistent gets again. The operation causes us to navigate the index tree to the start of GROUP_ID 3 and at that point we can stop as we have the minimum value of ID right there.

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s