Hints or tricks in SQL Statements

[jballum]

Hey,

I was wondering if anyone could give me some tips on forcing specific indexes to be used in select statements (or point me to some documentation on the subject). I encountered some yesterday that by changing something in the WHERE clause would speed up the query from 60 minutes to 2 minutes because a specific index was used like it should have been.

Thanx,

JB

P.S. Were hints (ie. /*+ INDEX tablename indexname*/ ) available in Version 7? I can't get my hands on any documentation other that some archived HTML stuff on Oracle Web site.

 

[karluk]

It looks as if you've answered your own question. If you want to force the use of a specific index, use an optimizer hint.

Optimizer hints were available in Oracle 7.

 

[sem]

The possible problem is in your hint:
/*+ index(tablename indexname)*/, where tablename must be the alias name if you use aliases.

 

[hacketta]

Hints are available in version 7 of Oracle but the optimiser will ignore them if the init.ora paramter optimiser_mode is set to RULE. Bear in mind that the follwing will prevent the optimiser from using an index:

-Using functions on the indexed column(s) in the WHERE clause
-Performing NULL comparisons in the WHERE clause on the indexed column(s) e.g. WHERE mycolumn IS NULL
-Using the NOT operator on the indexed column(s) in the where clause e.g. WHERE mycolumn NOT IN ('A','B')

Also bear in mind that Oracle may use a function without telling you should you, for example, include a string as a comparison on a numeric field