Re: Multi-column indexes vs Single Column Indexes

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... Received on Sat Aug 11 2007 - 21:53:02 CDT