Re: Index: order of fields

Jenda,
the order will depend on how you access the data. Since I wasn't clear about your example, let's use name, state, country_code (C/S). If you have one index with (Name, state, country_code) - the index will be used in the following queries

where name = 'Smith'
where name = 'Smith' and state = 'New York' where name like 'Smit%' and state = 'New York' and country = 'C'

only the name part of the index will be used in the following where name = 'Smith' and country = 'C'

No index will be used in
where state = 'New York'
where Upper(name) = 'SMITH' and state = 'New York' and country = 'C' (use of function disallows index use - if you're desperate for this though check on it - I'm only 90% sure)

and 'normal' index on country won't do you much good, a bit-map index might - although personal experience with bit-map indexes didn't buy my much.

also look up "autotrace" and "explain plan" for help on evaluating performance. table order in the 'from' clause of multi-table joins also makes a huge diff. generally want them in order of largest to smallest, since it joins tables and eliminates rows in reverse order from what's listed.

Cary

In article <1103_951125428_at_way>,
  Jenda_at_Krynicky.cz (Jenda Krynicky) wrote:
> What is the recomended order of fields in an index?
> I think it does matter right?
>
> Suppose I have these three fields I want to include in an index :
> Country : 'C' or 'S'
> Year : 1993-2000
> AGB : 1500 - 1700 (approx ... it's a number of weeks since some date)
>
> Now is it better to build the index with this order or just the
oposite .. or some other?
>
> Thanks, Jenda
> http://Jenda.Krynicky.cz
>
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Feb 25 2000 - 10:11:52 CST