Results 1 to 2 of 2

Thread: Composite Index

  1. #1
    Join Date
    Feb 2005

    Unanswered: Composite Index


    I have a composite index on table1 like ix_table1(col1,col2,col3).
    In my where clauses am using only col2. Will that composite index be used or do i need to create a seperate index on col2 ?



  2. #2
    Join Date
    Aug 2004
    If your where clauses always contain column2 and never column1, then you should consider placing column 2 before column1. The index would be more efficient for your queries. However, it will not ensure that it will be used : it will only be used if the optimizer considers it would be better to do so. Depending on data distribution, the optimizer may or may not consider that using this index is the best thing to do, and maybe it is right . It depends on many things...



    PS : what I just said about the optimizer is only true for the CBO (cost-based optimizer), not the RBO. I think that with the RBO, you will always use this index if it is the only one on column2, since for the RBO, Index-Scans are ALWAYS GOOD and Full-Table Scans are ALWAYS VERY BAD, which of course IS NOT TRUE : nothing is always true or false, except TRUE and FALSE .

Posting Permissions

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