Re: When does a query use an Index?

ratan wrote:
>
> 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

Suggestion (b) above seems to be unnecessary.

It is true that an index on (a,b,c,d) with values specified for (a,b,d) will be used like a non-unique index on (a,b) while traversing index brancheses. However, the value of column (d) will be used to filter the index leaf entries during the index scan without having to refer to column (c).

An exception this procedure occurs if column (c) is specified as null. In this case the index scan is NOT filtered. For example, suppose values are given explicitly for an index on (a,b,c,d) as:
a = somevalue
b is null
c is null
d is null

This has the unfortunate effect of requiring an index scan of all leaf entries with the specified value of (a) EVEN IF THE INDEX IS DECLARED AS UNIQUE!
I have seen a fully-specified unique index like the above take in excess of 10,000 index block reads to return a single values under the above conditions (all rows had the same value of (a)).

MORAL: Never include nullable columns in a unique index.

HTH   Chrysalis

FABRICATI DIEM, PVNC
('To Protect and to Serve')
Terry Pratchett : "Guards Guards" Received on Mon Sep 29 1997 - 00:00:00 CDT