General
cancel
Showing results for 
Search instead for 
Did you mean: 

Oracle: Does anyone know the syntax of the "NOINDEX" hint?

SOLVED
Go to solution
TwoProc
Honored Contributor

Oracle: Does anyone know the syntax of the "NOINDEX" hint?

I've been doing some tuning and testing with the /*+ INDEX... */ hint in Oracle.

Ex.
Select /*+ INDEX(ta,ta_N99) */
a,b,c
from tablea ta
where ta.a = 3
and ta.b = 'Joe';

Does anyone know how to TURN off an index? This would greatly simplify testing for comparison (rather than having to drop the index and recreate to test it both ways).

I'd like something like:
Select /*+ NOINDEX(ta) */
a,b,c
from tablea ta
where ta.a = 3
and ta.b = 'Joe';

I've tried the above, and it doesn't work.

Can anyone help with this?

While I'm at it - does anyone know where I can get a somewhat complete list of the hints available within SQLPlus/PLSql ?

Any help appreciated.
We are the people our parents warned us about --Jimmy Buffett
9 REPLIES
Arunvijai_4
Honored Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

This link may help you, http://www.adp-gmbh.ch/ora/sql/hints.html

(NO_INDEX ??? Haven't tried anything with that.)

-Arun
"A ship in the harbor is safe, but that is not what ships are built for"
Eric Antunes
Honored Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

Hi John,

The hints I know are (until Oracle 8i):

FULL
ROWID
CLUSTER
HASH
HASH_AJ
INDEX
INDEX_ASC
INDEX_COMBINE
INDEX_DESC
INDEX_FFS
MERGE_AJ
AND_EQUAL
USE_CONCAT

If you use the FULL one it passes the index...

Best Regards,

Eric Antunes
Each and every day is a good day to learn.
Piergiacomo Perini
Trusted Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

hi all,

the right sintax
is
/*+ NO_INDEX tablename indexname */


regards
Jean-Luc Oudart
Honored Contributor
Solution

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

Yogeeraj_1
Honored Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

hi,

RBO is dead! beware, with newer versions of Oracle we only have CBO.

read metalink note: 189702.1
Subject: Rule Based Optimizer is to be Desupported in Oracle10g


kind regards
yogeeraj
No person was ever honoured for what he received. Honour has been the reward for what he gave (clavin coolidge)
Arunvijai_4
Honored Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

http://download-west.oracle.com/docs/cd/B14117_01/server.101/b10759/toc.htm

-Arun
"A ship in the harbor is safe, but that is not what ships are built for"
TwoProc
Honored Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

Arun great links, thanks much.
Eric, appreciate the info, and FULL works too.
Piergiacomo, thanks I got it with a little tweaking:
Select /*+ NO_INDEX(ta,ta_N99) */
worked great.

Eric and Piergiacomo, it is interesting to note the difference between NO_INDEX and FULL. NO_INDEX tells the system not to use a particular index(in the example above, ta_N99), but the cost optimizer is still free to use the next best index the cost opimtimizer chooses. FULL tells the optimizer not to pick any index, perform a FULL table scan.
Both are great options, thanks much.

Jean-Luc thanks for the link.

Yogeeraj, thanks for reinforcing that RBO is leaving us, I must admit at times I liked managing somethings w/o statistics - in particular some tables that experience 100% change-outs every day. In some of those cases, many times it was just as fast and easier to force a RULE when you already knew what the execution plan should be.
We are the people our parents warned us about --Jimmy Buffett
Patti Johnson
Respected Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

John,

The easiest way I know is to modify the column in the where clause. For example
you have an index on emp_id (assume number column)

select * from emp
where emp_id+0 =

If emp_id is a varchar the
emp_id||''

Applying a function to the column will disable use of the index. (unless you have a function based index of course.)

It's an old trick from the RBO days, but it still works.

Patti
TwoProc
Honored Contributor

Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint?

Patti,
I hadn't thought of using that one, although I was aware that function changes to columns as part of a where clause would knock out the use of an index, and have to fight that one often. Just didn't think to use it as a way to purposely turn off the use of an index. Thanks much.
We are the people our parents warned us about --Jimmy Buffett