Adding a column to an index

[krw]

Within my database I have an index with two columns (col1,col2).

I want to add another column to the end of the index (col3) but want to know if this will effect any quiries which only use the original two columns (col1,col2) - will those queries stop using the index because col3 has been added? OR will original queries that only need (col1,col2) continue to use the index and the new query which i'm doing use the index for all 3 columns?

I'm trying to avoid creating to many indexes on this table as there is alot of inserts!!

Thanks in advance.

[pipo]

An index on (col1, col2, col3) will be used when queries use col1, col1 & col2, or even col1 & col3 (in this case, it will be used only on col1).

So it won't change anything for the queries using only col1 and col2

But note than adding column col3 in your index will only be useful if your queries using col3 ALSO use col1 & col2.

[jmodic]

Depends.

If you are using RBO then you are quite safe. Queries that use your index (col1,col2) will continue to use it when you add a third column to it (col1,col2,col3).

With CBO things are not so clear. Adding a column to an index means physicaly larger index and consequently more I/O operations to get the same information that was previously obtained from two column index. So for the same query optimizer may decide that now it is more expensive to use the index for the same query and choose to use full table scan instead, for example. It depends on many things. On the nature of the queries, on the avarage physical size of the index entres, on the quality of your statistics, on the release of the database, .....

You will have to test with your actual data, however IMHO there are very good chances the optimizer will continue to use the new index as it was using the old one before....

[DaPi]

Hi pipo & Jurij,

I've heard that in 9i (I don't use it yet) the "left to right" rule is circumvented somehow, allowing an index on (col1,col2) to be used for condition on col2 only. Am I dreaming? (Can't find the ref - bother!)

[jmodic]

No, you are not dreaming. It's true. It's called "skip-scan" or something like that.

[adewri]

Go to followup of second review for index skip scan

http://asktom.oracle.com/pls/ask/f?p...1156161921465,


HTH

[pipo]

In fact it's a hint : /*+ index_ss(table index) */
Thx Juri, I hadn't even heard of this new feature )



SCOPE & APPLICATION
-------------------

For developers and DBAs who need to know the syntax of the index skip scan hint.

Index skip scan Hint Usage
---------------------------

Index skip scans improve index scans by nonprefix columns since it is often faster to scan index blocks than scanning table data blocks.

In this case a composite index is split logically into smaller subindexes.

The number of logical subindexes depends on the cardinality of the initial column. Hence it is now possible to use the index even if the leading column is not used in a where clause.

[DaPi]

Thanks!