Search notes:
Oracle SQL Plan operation INDEX FULL SCAN feeding into FIRST ROW
create table tq84_tab (
num integer not null,
txt varchar2(3) not null,
val number(4,1) not null
);
create index tq84_tab_ix on tq84_tab(num, txt);
insert into tq84_tab
select
mod(level, 100),
dbms_random.string('U', 3),
dbms_random.value(1, 999)
from
dual connect by level <= 10000;
begin
dbms_stats.gather_table_stats(user, 'tq84_tab');
end;
/
explain plan for
select
max(num)
from
tq84_tab
where
txt = 'QUG'
;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- ---------------------------------------------------
-- | Id | Operation | Name |
-- ---------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | SORT AGGREGATE | |
-- | 2 | FIRST ROW | |
-- | 3 | INDEX FULL SCAN (MIN/MAX)| TQ84_TAB_IX |
-- ---------------------------------------------------
drop table tq84_tab;