Thanks, I'm unable to reproduce this though (see below).
Both commands, even the one with valid index hint, still go for FTSThe optimizer always obeys hints if it's possible to do so.
So this suggests there's some problem with the index itself. Is it disabled? Invalid? Invisible? Has someone fiddled with hidden parameters? It seems
something is preventing index usage.
create table t (
log_id number,
traceid varchar2 (250),
type varchar2 (250),
node varchar2 (250),
url varchar2 (4000),
token varchar2 (4000),
method varchar2 (250),
timestampreq varchar2 (100),
body clob,
response clob,
timestampres varchar2 (100),
rec_date date
) logging nocompress
lob (response) store as securefile
enable row movement
/
create index t_idx1 on t (
trunc (rec_date), type, node, method
)
/
set serveroutput off
select min(trunc(rec_date))
from t
where trunc(rec_date) is not null;
select *
from table(dbms_xplan.display_cursor());
--------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1 (100)| |
| 1 | SORT AGGREGATE | | 1 | 9 | | |
| 2 | FIRST ROW | | 1 | 9 | 1 (0)| 00:00:01 |
|* 3 | INDEX FULL SCAN (MIN/MAX)| T_IDX1 | 1 | 9 | 1 (0)| 00:00:01 |
--------------------------------------------------------------------------------------