Re: multiple indexes or a composite index?

<gdas_at_my-deja.com> wrote in message news:8obli1$q61$1_at_nnrp1.deja.com...
> I was just wondering if anyone has any general guidelines to follow
> that might help identify situations where one should use a composite
> index rather than multiple indexes on the individual columns. Focusing
> only on performance, rather than integrity...I've always been able to
> get faster queries and better execution plans when I've used separate
> indexes on the columns and I was wondering what situations are more
> suitable for performance improvements by way of a composite index?
>
> Thanks,
> Gavin
>
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.

Some general guidelines on indexes:
1 For all composite indexes: the most discriminating column must be leading 2 There is no reason why a composite index or a composite primary key should follow the exact sequence of column names for the table. 3 If the leading column of a composite index is the only column used in a where clause the index is still be used
4 You can force the use of a composite index by including a dummy criterion/dummy criterions on the non-leading column(s). 5 A typical case in point is the so-called junction table, existing of two columns, each column individually a foreign key to a different table. Here two keys should be used: columna, columnb as primary key and columnb, columna as unique key. You should not index columna alone. This index will not be used as it it non-unique.
You should be cautious with indexing columnb alone, as the preference for using unique indexes still applies.
6 all foreign keys should be indexed.
7 you should use a composite index when you *always* have queries with selection criteria for both columns.

Hth,

Sybrand Bakker, Oracle DBA Received on Sun Aug 27 2000 - 13:35:19 CDT