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: oracle can ignore hints

Re: oracle can ignore hints

From: Tim Gorman <tim_at_sagelogix.com>
Date: Wed, 10 Mar 2004 06:56:28 -0700
Message-ID: <BC746C9C.113FD%tim@sagelogix.com>


Were the hints understood by Oracle and ignored (as you imply but to which Cary, Jonathan, and Connor disagree) or were the hints merely unusable?

In particular, specifically which hints (i.e. INDEX, USE_NL, etc) do you think were ignored? What is the query? What was the execution plan? None of these questions involve sharing your data, just the query text and execution plan. Please feel free to ≥doctor≤ this information to hide any literals, table names, or column names as needed...

on 3/10/04 6:37 AM, ryan.gaffuri_at_cox.net at ryan.gaffuri_at_cox.net wrote:

> unless i make typos that i cannot see, I have seen oracle repeatedly ignore my
> hints lately. I cannot reproduce, since I cannot share our data.

>> >
>> > From: Jared.Still_at_radisys.com
>> > Date: 2004/03/10 Wed AM 08:22:22 EST
>> > To: oracle-l_at_freelists.org
>> > Subject: 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_at_triniti.com>
>> > Sent by: oracle-l-bounce_at_freelists.org
>> > 03/09/2004 10:53 PM
>> > Please respond to oracle-l
>> >
>> >
>> > To: oracle-l_at_freelists.org
>> > 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_at_radisys.com 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_at_triniti.com
>> > ( 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_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
>> -----------------------------------------------------------------

>> >
>> >
>> >
> 
> 
> 
> 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_at_triniti.com>
> Sent by: oracle-l-bounce_at_freelists.org  03/09/2004 10:53 PM
>  Please respond to oracle-l
>         
>         To:        oracle-l_at_freelists.org
>         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_at_radisys.com <mailto:Jared.Still_at_radisys.com>  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 




----------------------------------------------------------------
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 - 07:59:51 CST

Original text of this message

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