Re: Multi-column indexes vs Single Column Indexes

zigzagdna_at_yahoo.com wrote:
> I am using Orale9i and Oracle 10g on Windows and UNIX boxes.
> When creating new indexes which are non-unique, what are the
> pros and cons of creating a single index of multiple columns vs
> creating a separate index on each column.
>
> For example, if I created separate indexes: one on col1 and other
> on col2,
> Oracle could use these indexes if either col1 or col2 was used in
> queries.

And if both col1 and col2 are both used in a query, then it quite likely you will benefit from only use of those indices even when the joint of the two is highly selective compared to either one separately. That is the main benefit of an index on multiple columns--you get the join selectivity instead of just one or the other.

> However if I had an index on multiple columns (co1, col2), Oracle will
> not use
> Index if query used only col2. I know in Oracle9i, Oracle can use
> parts of multi-column index for col2, but this is not as efficient as
> having a separate index on col2. Disk space is not an issue for me, so
> I will be better off using a separate index for each column.

If you will use both col1 and col2 equalities in the where clause of the same query, it might be better to make one index on (col1,col2) and one on col2). Or maybe one on (col2,col1) and one on (col1), depending on the data and the uses to which you put it.

Obviously these are just generalities.

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB