Re: Index on more than one column

> Kjetil Skotheim wrote:
> >
> > If for instance I have done a
> >
> > create index on person (lastname, firstname)
> >
> > Will this index be ignored when I only use lastname= in a
> > where clause?
>
> If this is the only index on that table then Oracle will use the index. If
> there are several indexes then Oracle optimizer chooses the 'best' one.

according to _Tuning Oracle_, Corey,Abbey,Dechichio,jr, (pp190) : "A composite index will only be used to satisfy a query when the _leftmost_ column in the composite index is mentioned in the WHERE or AND clause"

this implies you may have to create distinct indexes, such that the ordering
of the fields in the composite index, will allow the index to be used if either of the fields is used in the where clause.

phil

-- 
Philip Fielder     YellNet 505.665.3227     Fac. for Info.,Management,
EES-5/MS D452      FaxNet  505.667.1628     Analysis and Display
FIMAD/Los Alamos Nat. Lab	 	           
Los Alamos, NM 87545                   URL : http://ees.lanl.gov/fimad