Re: Multi-column indexes vs Single Column Indexes

<hjr.pythian_at_gmail.com> wrote in message news:1186887182.684865.249580_at_g12g2000prg.googlegroups.com...
> On Aug 12, 11:50 am, zigzag..._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
>
> Every index means extra work when you do DML on the table. You have to
> weigh these things up. Skip-scanning of indexes means reasonably
> efficient access to the Column2 of the single index, but only one
> index to maintain. Separate indexes mean (perhaps) single block access
> to the index, but multiple indexes to maintain. Two lots of redo, two
> lots of undo, two lots of latching and locking...
>
> It's what performance tuning is all about, really...

Then again, having only one index to maintain doesn't necessarily mean skip-scanning will occur at all as it requires the cardinality of the leading column to be somewhat greater than two times the average number of rows referenced per index leaf page for it to be even considered by the CBO. In many cases, this simply rules out skip-scanning as a viable option.

Although I agree more indexes makes subsequent DML less efficient, the general guideline is that a row is inserted once, is generally updated rarely if at all, is deleted rarely or once if at all but can be queried continually for years and years. Therefore if an index means business functionality can be resolved instantaneously or dramatically quicker than without the index, then the justification of having whatever indexes are necessary to satisfy business requirements usually overwrite the (generally) unnoticed overheads from the business perspective of maintaining such indexes.

Cheers

Richard Received on Sun Aug 12 2007 - 04:56:56 CDT