Oracle SQL Plan operation: WINDOW (NOSORT STOPKEY)
create table tq84_tab (
num number,
dat date,
val varchar2(100)
);
--
-- Without the following index, the plan
-- operation WINDOW SORT PUSHED RANK would be used.
--
create index tab_ix on tq84_tab(num, dat);
explain plan for
select
dat,
val
from
tq84_tab
where
num = 42
order by
dat
fetch first 2 row only;
select * from table(dbms_xplan.display(format=>'basic'));
--
-- --------------------------------------------------
-- | Id | Operation | Name |
-- --------------------------------------------------
-- | 0 | SELECT STATEMENT | |
-- | 1 | VIEW | |
-- | 2 | WINDOW NOSORT STOPKEY | |
-- | 3 | TABLE ACCESS BY INDEX ROWID| TQ84_TAB |
-- | 4 | INDEX RANGE SCAN | TAB_IX |
-- --------------------------------------------------
drop table tq84_tab;