Using an Index to Shortcut a MIN/MAX Aggregate

I was recently discussing with a developer whether a “SELECT MIN/MAX” could benefit from an index on the aggregated column. In the case I was asked about it would be of no help, however I went on to explain where it could be useful and thought I’d reproduce it here as my introductory blog post. I expect this has been well documented elsewhere but it’s a good note for me to practice formatting blog posts on.

First off create a table of a reasonable size

create user mintest identified by mintest default tablespace users;
grant connect, resource to mintest;
grant plustrace to mintest;

conn mintest/mintest

create table tab1 as select rownum rno, object_name, object_type from all_objects;
insert into tab1 select rownum+50000,  object_name, object_type from tab1;
commit;
insert into tab1 select rno+100000,  object_name, object_type from tab1;
insert into tab1 select rno+200000,  object_name, object_type from tab1;
insert into tab1 select rno+300000,  object_name, object_type from tab1;
commit;

create index mintesti on tab1(rno);

exec dbms_stats.gather_table_stats(null,'tab1',cascade=>true);

Now let’s see how 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 (note the “FULL SCAN (MIN/MAX)”)

set autotrace on
select min(rno) from tab1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=324 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     INDEX (FULL SCAN (MIN/MAX)) OF 'MINTESTI' (NON-UNIQUE) (Cost=324 Card=342448 Bytes=1712240)

Statistics
----------------------------------------------------------
          3  consistent gets
          0  physical reads

If we select bot a MIN and MAX in the same query however then the optimiser can’t cope and scans the whole table.

set autotrace on
select min(rno), max(rno) from tab1;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=324 Card=1 Bytes=5)
   1    0   SORT (AGGREGATE)
   2    1     TABLE ACCESS (FULL) OF 'TAB1' (Cost=324 Card=342432 Bytes=1712160)

Statistics
----------------------------------------------------------
       2128  consistent gets
        129  physical reads

You can still trick it though by querying the table twice.

select min_rno, max_rno 
from 	(select min(rno) min_rno from tab1) m1
,	(select max(rno) max_rno from tab1) m2;

Execution Plan
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=648 Card=1 Bytes=26)
   1    0   MERGE JOIN (CARTESIAN) (Cost=648 Card=1 Bytes=26)
   2    1     VIEW (Cost=324 Card=1 Bytes=13)
   3    2       SORT (AGGREGATE)
   4    3         INDEX (FULL SCAN (MIN/MAX)) OF 'MINTESTI' (NON-UNIQUE) (Cost=324 Card=342432 Bytes=1712160)
   5    1     FIRST ROW
   6    5       VIEW (Cost=324 Card=1 Bytes=13)
   7    6         SORT (AGGREGATE)
   8    7           INDEX (FULL SCAN (MIN/MAX)) OF 'MINTESTI' (NON-UNIQUE) (Cost=324 Card=342432 Bytes=1712160)

Statistics
----------------------------------------------------------
          6  consistent gets
          0  physical reads

This was tested at 9i, 10g and 11g.

So that’s my first post done with. I’ll try to come up with something more original next time 🙂

Advertisements

One thought on “Using an Index to Shortcut a MIN/MAX Aggregate

  1. Pingback: Using an Index to Shortcut a MIN/MAX Aggregate – Part 2 « ORAganism

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