Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: RE: oracle can ignore hints

RE: RE: oracle can ignore hints

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 10 Mar 2004 10:01:19 -0500
Message-ID: <D91D9D5A73FC694BBC52F1EB26AD410F02780CA5@MSGBOSCLD2WIN.DMN1.FMR.COM>


A hint that will cause incorrect results to be returned if it was followed, can not be considered a valid hint.  

Waleed    

-----Original Message-----
From: Jared.Still_at_radisys.com [mailto:Jared.Still_at_radisys.com] Sent: Wednesday, March 10, 2004 12:59 AM To: oracle-l_at_freelists.org
Subject: Re: 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

        <ryan.gaffuri_at_cox.net>
Sent by: oracle-l-bounce_at_freelists.org

 03/09/2004 10:30 AM
 Please respond to oracle-l

        
        To:        oracle-l_at_freelists.org 
        cc:         
        Subject:        Re: RE: oracle can ignore hints



so in your opinion hints cannot be ignored?
>
> From: "Cary Millsap" <cary.millsap_at_hotsos.com>
> Date: 2004/03/09 Tue AM 11:02:24 EST
> To: <oracle-l_at_freelists.org>
> Subject: RE: oracle can ignore hints
>
> For what it's worth, after spending time with Jonathan Lewis and Connor
> McDonald in the past two days, I am deeply and thoroughly convinced that
> hints are directives, not suggestions.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *
>
> Upcoming events:
> - Performance Diagnosis 101: 3/23 Park City, 4/6 Seattle
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of R Zijlstra
> Sent: Monday, March 08, 2004 7:55 AM
> To: oracle-l_at_freelists.org
> Subject: RE: oracle can ignore hints
>
> Excellent indeed...
> As far as I now understand, Oracle can use a hint or it can use it not.
> The
> 'hinting behaviour' is not predictable, but it doesn't seem to hinder
> 'normal (?)' execution of sql.
>
> Might it be possible, that hints are somehow tied in with a marketing
> policy
> when Oracle needed something to talk about??
>
> Rob Zijlstra
> -----------------------------
>
>
> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]
> On Behalf Of Cary Millsap
> Sent: Monday, March 08, 2004 2:39 PM
> To: oracle-l_at_freelists.org
> Subject: RE: oracle can ignore hints
>
> Excellent!
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
> * Nullius in verba *



Please see the official ORACLE-L FAQ: http://www.orafaq.com

To unsubscribe send email to: oracle-l-request_at_freelists.org 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
-----------------------------------------------------------------
Received on Wed Mar 10 2004 - 09:09:22 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US