Re: Re: how to get oracle to ignore an index

From: Jonathan Lewis <jonathan_at_jlcomp.demon.co.uk>
Date: Fri, 6 Feb 2004 20:48:28 -0000
Message-ID: <00a801c3ecf2$93415900$6702a8c0@Primary>

You have a little lee-way with that, and a great pit of confusion. I have a script called hint_hoho.sql which shows the following:

select n2 from t2 where n1 = 33;

Full tablescan

select /*+ index(t2) */ n2 from t2 where n1 = 33;

Indexed access into t2

select /*+ a complete hint index(t2) */ n2 from t2 where n1 = 33;

Indexed access into t2

select /*+ not a complete hint index(t2) */ n2 from t2 where n1 = 33;

Full tablescan

select /*+ (t1) index(t2) */ n2 from t2 where n1 = 33;

Full tablescan

select /*+ full(t1) index(t2) */ n2 from t2 where n1 = 33;

Indexed access into t2

Version 9.2.0.4
You can get away with some thing which you shouldn't get away with, and you get hit by some things that you don't expect to be a problem.

If Oracle spots something that looks like a damaged hint, it seems to ignore the whole set of hints.

If Oracle sees a load of text that clearly is nothing to do with hinting, it accepts the hints.

If Oracle sees a hint which is correct in form, but completely irrelevant, it ignores it, but is happy with the rest of the hints.

YMMV - YHMFO 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: "Wolfgang Breitling" <breitliw_at_centrexcc.com> To: <oracle-l_at_freelists.org> Sent: Friday, February 06, 2004 6:02 PM Subject: Re: Re: how to get oracle to ignore an index

Also, if you have multiple hints and have a syntax error somewhere, not just the malformed hint but also everything following is 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
-----------------------------------------------------------------

Received on Fri Feb 06 2004 - 14:48:28 CST