Adding a column to an index
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 8 of 8

Thread: Adding a column to an index

  1. #1
    Join Date
    Jan 2003
    Posts
    35

    Arrow Adding a column to an index

    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.

  2. #2
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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.

  3. #3
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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....
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  4. #4
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142
    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!)
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

  5. #5
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    No, you are not dreaming. It's true. It's called "skip-scan" or something like that.
    Jurij Modic
    ASCII a stupid question, get a stupid ANSI
    24 hours in a day .... 24 beer in a case .... coincidence?

  6. #6
    Join Date
    Nov 2002
    Location
    New Delhi, INDIA
    Posts
    1,796
    Go to followup of second review for index skip scan

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


    HTH
    Amar
    "There is a difference between knowing the path and walking the path."

    Amar's Blog  Get Firefox!

  7. #7
    Join Date
    Feb 2001
    Location
    Paris, France
    Posts
    809
    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.

  8. #8
    Join Date
    Nov 2002
    Location
    Geneva Switzerland
    Posts
    3,142

    Thumbs up

    Thanks!
    "The power of instruction is seldom of much efficacy except in those happy dispositions where it is almost superfluous" - Gibbon, quoted by R.P.Feynman

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width