Does Oracle ignore hints – not if you use them correctly, and sometimes it doesn’t ignore them even when you use them incorrectly!
Here’s an example that I’ve run on 220.127.116.11 and 18.104.22.168
create table t1 as with generator as ( select --+ materialize rownum id from dual connect by level <= 1e4 ) select rownum id, rownum n1, rpad('x',100) padding from generator v1 ; begin dbms_stats.gather_table_stats( ownname => user, tabname =>'T1', method_opt => 'for all columns size 1' ); end; / create index t1_i1 on t1(id); alter index t1_i1 unusable; select n1 from t1 where id = 15; select /*+ index(t1 (id)) */ n1 from t1 where id = 15;
Any guesses about the output from the last 4 statements ?
Index created. Index altered. N1 ---------- 15 1 row selected. select /*+ index(t1 (id)) */ n1 from t1 where id = 15 * ERROR at line 1: ORA-01502: index 'TEST_USER.T1_I1' or partition of such index is in unusable state
That’s a pretty convincing display of Oracle not ignoring hints.
Technically, of course, I haven’t demonstrated that Oracle is not ignoring the hint (i.e. that it’s obeying the hint – if you want to avoid the double negative) until I demonstrate that in the absence of the hint the error would not occur – but that task is left as an exercise to the reader.