Selecting both MIN and MAX From the Table is slower than expected

Question

I have a table MYTABLE with a date column SDATE which is the primary key of the table and has a unique index on it.

When I run this query:

SELECT MIN(SDATE) FROM MYTABLE

it gives answer instantly. The same happens for:

SELECT MAX(SDATE) FROM MYTABLE

But, if I query both together:

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE

it takes much more time to execute. I analyzed the plans and found when one of min or max is queried, it uses INDEX FULL SCAN(MIN/MAX) but when both are queried at the same time, it does a FULL TABLE SCAN.

why?

Test Data:

version 11g

create table MYTABLE
(
  SDATE  DATE not null,
  CELL   VARCHAR2(10),
  data NUMBER
)
tablespace CHIPS
  pctfree 10
  pctused 40
  initrans 1
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

alter table MYTABLE
  add constraint PK_SDATE primary key (SDATE)
  using index 
  tablespace SYSTEM
  pctfree 10
  initrans 2
  maxtrans 255
  storage
  (
    initial 64K
    minextents 1
    maxextents unlimited
  );

Load table:

declare 
  i integer;
begin
  for i in 0 .. 100000 loop
     insert into MYTABLE(sdate, cell, data)
     values(sysdate - i/24, 'T' || i, i);     
     commit;
  end loop;
end;

Gather stats:

begin
  dbms_stats.gather_table_stats(tabname => 'MYTABLE', ownname => 'SYS');
end;

Plan1:

Plan2:

Answer 1

The Index Full Scan can only visit one side of the index. When you are doing

SELECT MIN(SDATE), MAX(SDATE) FROM MYTABLE 

you are requesting to visit 2 sides. Therefore, if you want both the minimum and the maximum column value, an Index Full Scan is not viable.

A more detailed analyze you can find here.

Answer 2

The explain plans are different: a single MIN or MAX will produce a INDEX FULL SCAN (MIN/MAX) whereas when the two are present you will get an INDEX FULL SCAN or a FAST FULL INDEX SCAN.

To understand the difference, we have to look for a description of a FULL INDEX SCAN:

In a full index scan, the database reads the entire index in order.

In other words, if the index is on a VARCHAR2 field, Oracle will fetch the first block of the index that would contain for example all entries that start with the letter "A" and will read block by block all entries alphabetically until the last entry ("A" to "Z"). Oracle can process in this way because the entries are sorted in a binary tree index.

When you see INDEX FULL SCAN (MIN/MAX) in an explain plan, that is the result of an optimization that uses the fact that since the entries are sorted, you can stop after having read the first one if you are only interested by the MIN. If you are interested in the MAX only, Oracle can use the same access path but this time starting by the last entry and reading backwards from "Z" to "A".

As of now, a FULL INDEX SCAN has only one direction (either forward or backward) and can not start from both ends simultaneously, this is why when you ask for both the min and the max, you get a less efficient access method.

As suggested by other answers, if the query needs critical efficiency, you could run your own optimization by searching for the min and the max in two distinct queries.