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.
> 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.
>
>
> Thanks

You've had responses from two of the best in the business, Howard Rogers and Richard Foote, but their both in Australia so likely asleep now, moving me to throw in a slightly different, though perhaps less academic, response.

There is no single answer to your question ... no magic bullet ... the only way to answer your question is to build both types and test how Oracle will use them with your data and your optimizer settings (such as optimizer_index_cost_adj and optimizer_index_caching).

If it is a production system that will not allow you to create and drop indexes at will; at least in 10g you can use nosegment indexes. There is a demo on this at: http://www.psoug.org/reference/indexes.html#ixns. The cost of building a nosegment index, for all practical purposes is zero.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu (replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org