Re: oracle can ignore hints

Correct, so did Oracle 'ignore' the hint?

I guess the point is this:  does Oracle ignore your hints,
or are they merely unusable?

You could also try this with a bitmapped index.

Jared






Vasu Balla <vballa@xxxxxxxxxxx>
Sent by: oracle-l-bounce@xxxxxxxxxxxxx
 03/09/2004 10:53 PM
 Please respond to oracle-l

 
        To:     oracle-l@xxxxxxxxxxxxx
        cc: 
        Subject:        Re: oracle can ignore hints



Hi Jared,

i think, a index will not have a row pointer having a null for indexed 
column. Thats the reason the index is ignored in first case, where you are 
checking for null. i may be wrong also

Vasu

Jared.Still@xxxxxxxxxxx wrote:

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 

-- 
----------------------------------------------------- 
Vasu Balla
* email : vballa@xxxxxxxxxxx 
( phone :+91 40 27893939 X 1291
-----------------------------------------------------
---------------------------------------------------------------- Please 
see the official ORACLE-L FAQ: http://www.orafaq.com 
---------------------------------------------------------------- To 
unsubscribe send email to: oracle-l-request@xxxxxxxxxxxxx put 
'unsubscribe' in the subject line. -- Archives are at 
http://www.freelists.org/archives/oracle-l/ FAQ is at 
http://www.freelists.org/help/fom-serve/cache/1.html 
----------------------------------------------------------------- 

Other related posts: