| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re: how to get oracle to ignore an index
Ryan
Here is the way I look at the issue you raise. Hints are coded as comments, not a part of the SQL statement. If a hint has bad syntax, Oracle does not error out, but treats the mangled hint as a comment. If the hint refers to an index that doesn't exist, Oracle does not error out. That is what I think is meant by saying that Oracle can ignore a hint if it chooses to.
But if Oracle doesn't respond to a hint, rather than assuming that Oracle doesn't want to use your hint, a better approach is to persist in trying to figure out what is wrong with your hint. The easiest error is to have bad syntax. The next easiest is to hint something dumb.
Since Oracle doesn't return an error, your feedback is limited. In my years in I.T., I have found situations where no error is returned or the wrong error is returned are among the most frustrating and discouraging. If I may be indulged a trip down memory lane, I had an early home computer, the Radio Shack Color Computer. I wanted to learn assembly programming. You could hand-assemble some machine instructions, POKE them into memory, then jump execution to that location. I followed the manual's instructions and received an error. I tried and tried and finally gave up. Months later a friend says "yeah an error is always returned because you usually mangle the register BASIC uses to tell if it has an error -- ignore the error". I sure wish the manual had mentioned that. So just think of this list as a small, planet-wide, group of friends that can clear up misconceptions.
Dennis Williams
DBA
Lifetouch, Inc.
dwilliams_at_lifetouch.com
-----Original Message-----
From: ryan.gaffuri_at_cox.net [mailto:ryan.gaffuri_at_cox.net]
Sent: Friday, February 06, 2004 1:31 PM
To: oracle-l_at_freelists.org
Subject: Re: Re: how to get oracle to ignore an index
ok thanks. I guess i mis-read the articles. got the hint you said was wrong out of the O'Reilly Oracle SQL Tuning handbook. I just double checked on OTN. My bad on that one.
thanks for the corrections.
>
> From: "Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk>
> Date: 2004/02/06 Fri PM 02:12:13 EST
> To: <oracle-l_at_freelists.org>
> Subject: Re: Re: how to get oracle to ignore an index
>
>
> Notes in-line
>
> Regards
>
> Jonathan Lewis
> http://www.jlcomp.demon.co.uk
>
> The educated person is not the person
> who can answer the questions, but the
> person who can question the answers -- T. Schick Jr
>
>
> Next public appearances:
> March 2004 Hotsos Symposium - The Burden of Proof
> March 2004 Charlotte NC OUG - CBO Tutorial
> April 2004 Iceland
>
>
> One-day tutorials:
> http://www.jlcomp.demon.co.uk/tutorial.html
>
>
> Three-day seminar:
> see http://www.jlcomp.demon.co.uk/seminar.html
> ____UK___February
> ____UK___June
>
>
> The Co-operative Oracle Users' FAQ
> http://www.jlcomp.demon.co.uk/faq/ind_faq.html
>
>
> ----- Original Message -----
> From: <ryan.gaffuri_at_cox.net>
> To: <oracle-l_at_freelists.org>
> Sent: Friday, February 06, 2004 6:28 PM
> Subject: Re: Re: how to get oracle to ignore an index
>
>
> i cant reproduce the data for you. its internal. I tested it repeatedly. I
> was very careful about the hint.
>
> select /*+ index(<index_name>) */
>
> >>> The thing above is not a valid hint
>
> select /*+ index(<table> <index_name>) */
>
> >>> The thing above might not be a valid hint
> >>> as the <table> might have been given an alias
> >>> If the table does not have an alias, then this
> >>> will be obeyed, unless some conditions relating
> >>> to null columns could make the indexed path
> >>> return the wrong path, or (prior to skip scans)
> >>> there was no optimizer mechanism for using
> >>> the index.
>
> Tried it repeatedly. I've seen it before. Sorry, I cant show you the data,
> etc... I could have swarn I read in the docs that hints are just
directives
> and oracle does not have to use them? I cant find it right now. Am I
wrong?
> I'm pretty sure I have seen that several times.
>
> >>> There are some bugs relating to execution methods
> >>> that require some detailed examination of 10053
> >>> trace files to determine how Oracle pushed an
> >>> 'obvious' hint out of context. Apart from these,
> >>> and there are some 'feature-related' hints that
> >>> can only work if various init.ora parameters
> >>> are set ... and so on.
> >>>
> >>> You probably have read that hints are 'just'
> >>> directives (to me a directive is the same as
> >>> an order, by the way). I've seen it in a Metalink
> >>> note somewhere. You will have found numerous
> >>> articles which say that hints can be ignored.
> >>> But in recent versions I don't think I've been
> >>> presented with an example where a valid hint
> >>> was ignored.
> >>
>
>
> ----------------------------------------------------------------
> 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
> -----------------------------------------------------------------
>
-- Archives are at http://www.freelists.org/archives/oracle-l/ FAQ is at http://www.freelists.org/help/fom-serve/cache/1.html ----------------------------------------------------------------- ---------------------------------------------------------------- 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 Fri Feb 06 2004 - 14:03:37 CST
 |
 |