Re: Indexng Tables

Shimsoft wrote:
>
> How do you build a composite index for a table. Namely, how do you determine the best order for the columns?

putting the most selective column (the one with the most distinct values) first may be slightly better for performance, but I doubt it would be very noticeable.

It will be noticeable if you are doing searches on a subset of the columns in the index. You can only use the index columns at the beginning of the index, so put the column(s) you use most at the beginning.
e.g.

create index itest on test (col1, col2, col3);

select * from test where col1 = 'x';
select * from test where col1= 'x' and col2 = 'y';

        Will both use the index whereas
select * from test where col2 = 'y';

        Will NOT (can't) use the index.

-- 
---
Allen Kirby			AT&T ITS Production Services
akirby_at_att.com			Alpharetta, GA.