Re: How to give hints about indizes ??

For having the Oracle optimizer recognize hints, use the following code:

select /*+ FULL(TABLE_NAME) */
.......

This tells the optimizer to do a full table scan on the indicated table. The syntax looks just like a comment, but with the addition of a plus sign immediately following the comment opener (no spaces).

If you mistype the hint, Oracle just ignores it without any warning messages, so if the query is not doing what you expect, check the syntax of your hint.

Hope this helps.

Mark Prebilic
PrebSon Consulting

Michael Keppler wrote:

> Hello everyone !
>
> I'm more an application programmer than a database admin and I
> recognized that some of my queries execute faster when I use
> ...
> and column<some_parameter+1
> and column>some_parameter-1
> ...
>
> instead of
> and column=some_parameter
>
> on an indexed integer column
>
> I guess it's because the optimizer tries to use an index on that column
> in the second solution. I know that this column isn't very selective
> (maybe I select about 30% of all values), so it would be better if the
> optimizer would use an index on another column (the query is about 50 or
> more lines). How can I tell the optimizer not to use an index on that
> column in this query ?
> And is there a possibility to automatically test some variations of
> different optimizations so I can see which is the fastest ?
> And where can I find out more about hints for the optimizer in general ?
> Are there some good web pages ?
>
> Ciao and Thanks, Michael.
>
> --
> Michael Keppler, MCSE, MCP+I
> IT logic GbR
> Michael.Keppler_at_gmx.de
Received on Fri Oct 29 1999 - 13:08:49 CDT