Re: When does a query use an Index?

John Smith wrote:
>
> I have looked through all the books I have and can't find a definitive
> answer.
>
> 1. Does a query use an Index when LIKE is used?
>
> 2. Does a query use an Index when an outer join is performed?
>
> 3. Does an Index get used when a view is used? The book says 'under
> certain conditions an Index will not be used' but doesn't say what they
> are.
>
> Can anyone answer these please?
>
> Thanks,
>
> Andy Horne

In addition to the earlier posts which are all very true: (a) You can specify a hint to use a particular index when you have more than one index defined on your table.
(b) Remember that the index is used only as to the extent of the indexed columns mentioned in the WHERE clause. Also, if for eg: your index consists of columns A,B,C and D and your WHERE clause has A,B,D involved, then the index will be used only as far as A,B. D will not get used because there was a break, in not using column C. So, try to use column C by specifying somecondition which will always evaluate to true(note that no function should be involved), or create a separate index with A,B, and D and use that in the hint.

Hope this helps.

-Ratan Received on Fri Sep 26 1997 - 00:00:00 CDT