This is Oracle 11.2.0.4, no upgrade to 12c before long.I have a table with 944 milions rows with columns set as :<br>table TRANSACT ( FDATE NOT NULL varchar2(8), -- a string format YYYYMMDD, on avg 1.3 millions rows every day TX_ID NOT NULL varchar2(30), -- transaction id absolutely unique string of 30 char TX_ID_SEQ NOT NULL number, -- sequence 1..999 .. ( 30 cols) . There is an index on these 3 columns.The table is not partitioned, no partition option in DB. Every day purge transactions older than 2 years so I retrieve the oldest day from the table and use DBMS_PARALLEL_EXECUTE to delete in parallel. The purge itself works fine, but the determination of the oldest day to proceed takes too much resources: it is always a Fast FULL INDEX SCAN of 70g to return 1 row. On average 1.3 millions rows will satify the following query : select min(FDATE) from transact ; -------------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | ----------------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | | 1 | SORT AGGREGATE | | 1 | 9 | | | | 2 | INDEX FULL SCAN (MIN/MAX)| TRANSACT_2 | 1 | 9 | 4 (0)| 00:00:01 | -----------------------------------------------------------------------------------------and I got a full index scan on the index wich is 70 Giga big. It seems that Oracle retieves all rows of the lowest date before returning the one result. This is probably because 1.3 million rows satisfy the condition MIN(FDATE). Though execution plan (taken from v$sql_plan_statistics) shows an optimal execution, in reality it does perform a FFS: This is from SQL monitor, after 38 seconds runs (If I let it go, it takes one hour): SQL Monitoring Report SQL Text ------------------------------ select min(FDATE) from TRANSACT Global Information ------------------------------ Status : EXECUTING Instance ID : 1 Session : P0957 (3305:49291) SQL ID : dqu44d4pfm0um SQL Execution ID : 16777216 Execution Started : 09/21/2016 10:01:28 First Refresh Time : 09/21/2016 10:01:32 Last Refresh Time : 09/21/2016 10:02:06 Duration : 40s Module/Action : SQL*Plus/- Global Stats ========================================================= | Elapsed | Cpu | IO | Buffer | Read | Read | | Time(s) | Time(s) | Waits(s) | Gets | Reqs | Bytes | ========================================================= | 38 | 1.70 | 37 | 340K | 48790 | 381MB | ========================================================= SQL Plan Monitoring Details (Plan Hash Value=3629939381) ======================================================================================================================================================================= | Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Activity | Activity Detail | | | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | (%) | (# samples) | ======================================================================================================================================================================= | 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | 1 | SORT AGGREGATE | | 1 | | | | 1 | | | | | | | -> 2 | INDEX FULL SCAN (MIN/MAX) | TRANSACT_2 | 1 | 4 | 40 | +0 | 1 | 0 | 44278 | 346MB | 100.00 | Cpu (1) | | | | | | | | | | | | | | db file sequential read (39) | ======================================================================================================================================================================== Variation 1: select FDATE from ( select /*+ index(a TRANSACT_2) */ FDATE from TRANSACT a order by FDATE ) where rownum = 1 ;-------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | -------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | 4 (100)| | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 1 | 6 | 4 (0)| 00:00:01 | | 3 | INDEX FULL SCAN| TRANSACT_2 | 1 | 9 | 4 (0)| 00:00:01 | -------------------------------------------------------------------------------- Again a Full SCAN of 70 G ................ Variation 2: ................ select MIN(FDATE) from ( select MIN(FDATE) over ( partition by FDATE order by FDATE, TX_ID) FDATE, row_number() over ( partition by FDATE order by FDATE, TX_ID) rn FROM TRANSACT ) where rn = 1 ; ---------------------------------------------------------------------------- | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | |* 1 | VIEW | | 944M| | | | | 2 | WINDOW BUFFER | | 944M| 25G| 50M| | | 3 | INDEX FULL SCAN| TRANSACT_2 | 944M| | | | ---------------------------------------------------------------------------- Again a Full SCAN of 70 G ................ variation 3: ................select FDATE from ( select /*+ index_rs_asc(a TRANSACT_2) */ FDATE from TRANSACT a order by FDATE ) where rownum = 1 ---------------------------------------------------------------------------------- | Id | Operation | Name | E-Rows | OMem | 1Mem | Used-Mem | ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | | | | | |* 1 | COUNT STOPKEY | | | | | | | 2 | VIEW | | 944M| | | | |* 3 | SORT ORDER BY STOPKEY| | 944M| 9116M| 29M| | | 4 | INDEX FAST FULL SCAN| TRANSACT_2 | 944M| | | | ---------------------------------------------------------------------------------- I only want to get the first row of the fist block referenced into the index. Should take 0.01s but takes 1hour. But given the fact that each day there are 1.3 millions rows with the same FDATE, Oracle opt for the FULL INDEX SCAN or FFIS. Obviously the MIN/MAX and stop key does not work here. Any suggestion ?