Cheesy Code

To demonstrate the MIN and MAX functions let’s create a global temporary table with an index and then add some data to the table. Note that you must create indexes on temporary tables when they are empty so you’ll have to TRUNCATE the table if there is anything in it. Let’s create the table with an index on the id column and then check that the index was created:

CREATE GLOBAL TEMPORARY TABLE rnd_message(id NUMBER(10), dte DATE)
ON COMMIT DELETE ROWS;

CREATE INDEX ix_msg_01 ON rnd_message(id);

SELECT index_name, table_name, column_name, column_position
  FROM ALL_IND_COLUMNS
 WHERE index_owner = SYS_CONTEXT('USERENV', 'CURRENT_USER');

Then we’ll randomly generate a 1,000,000 rows for the table:

EXEC dbms_random.initialize(123);

INSERT INTO rnd_message
SELECT lvl, dte FROM
    (
    SELECT LEVEL lvl, TRUNC(SYSDATE)-7 + dbms_random.value(0,8) dte
      FROM dual
    CONNECT BY LEVEL <= 1000000
    );

Since this is a temporary table we can't gather statistics for it using dbms_stats but it doesn't matter for this example. Let's have a look at some plans, first just a MIN:

SELECT MIN(id) FROM rnd_message;

Which uses a MIN/MAX scan. So now let's try both a MIN and a MAX:

SELECT MIN(id), MAX(id) FROM rnd_message;

Wow! So the optimizer has decided it needs a value from the start of the table and one from the end - so may as well just scan the whole table! With big tables this can be quite slow. If you want to force two MIN/MAX scans instead of a full table scan or an index fast full scan then you'll have to write something like this:

SELECT (SELECT MIN(id) val FROM rnd_message) mn,
       (SELECT MAX(id) val FROM rnd_message) mx
  FROM dual;

Which yields the expected 2 x MIN/MAX scans, which is vastly better.

Using autotrace we can find the consistent gets for the two methods to see how they perform:

So there's a big difference even here, 2480 gets compared to only 9 for a temporary table with only a million records so on production sized tables you can expect even worse performance. Be careful when using both min and max or you might get unexpectedly poor performance. Tested on both 9i and 11gR1 - similar execution plans are generated for both.