topic Re: Oracle: Does anyone know the syntax of the "NOINDEX" hint? in Operating System - HP-UX

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

TwoProc — Thu, 20 Oct 2005 08:11:44 GMT

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.

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

Arunvijai_4 — Thu, 20 Oct 2005 08:15:04 GMT

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

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

-Arun

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

Eric Antunes — Thu, 20 Oct 2005 08:23:35 GMT

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

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

Piergiacomo Perini — Thu, 20 Oct 2005 08:26:50 GMT

hi all,

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

regards

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

Jean-Luc Oudart — Thu, 20 Oct 2005 08:46:21 GMT

Hi

doucmentation :
http://download-uk.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements006.htm#sthref654

Regards
Jean-Luc

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

Yogeeraj_1 — Thu, 20 Oct 2005 09:00:04 GMT

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

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

Arunvijai_4 — Thu, 20 Oct 2005 09:00:09 GMT

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

-Arun

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

TwoProc — Thu, 20 Oct 2005 10:00:54 GMT

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.

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

Patti Johnson — Thu, 20 Oct 2005 10:01:58 GMT

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

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

TwoProc — Thu, 20 Oct 2005 10:13:20 GMT

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.