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:
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.