Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Mailing Lists -> Oracle-L -> Re: oracle can ignore hints
It would be nice to see a reproducible test case of a hint being ignored.
Here's one you can try:
drop table t;
create table t( c1 varchar2(30));
create index tidx on t(c1);
insert into t values(null);
commit;
exec dbms_stats.gather_table_stats(user,'T')
set autotrace on
select /*+ index(t tidx) */
c1
from t
where c1 is null
/
set autotrace off
update t set c1 = 'DATA';
commit;
exec dbms_stats.gather_table_stats(user,'T')
set autotrace on
select /*+ index(t tidx) */
c1
from t
where c1='DATA'
/
select
c1
from t
where c1='DATA'
/
set autotrace off
You will notice that the hint in the first query is 'ignored'.
If you do a 10053 trace on the first query you will see that the index TIDX is not
even considered for use as an access path. Does this mean that the index
hint was ignored?
The second query uses the index because the hint tells the CBO to do so. The
third query is a FTS because that's what the CBO would rather do.
HTH
Jared