Fundamental Index question
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: Fundamental Index question

  1. #1
    Join Date
    Nov 2011
    Posts
    2

    Fundamental Index question

    One of the Oracle databases I work with has an index on a table that spans 6 attributes. Someone now wants to create another index on the same table that is comprised of 3 of the attributes that are part of the first index.

    Does that make any sense? How can that be helpful, couldn't the db simply use the first index to get the performance benefit they are looking for? I'm puzzled by this but don't want to object if there is actually a good reason for doing this.

  2. #2
    Join Date
    Mar 2007
    Location
    Ft. Lauderdale, FL
    Posts
    3,554
    Quote Originally Posted by OldTimer View Post
    One of the Oracle databases I work with has an index on a table that spans 6 attributes. Someone now wants to create another index on the same table that is comprised of 3 of the attributes that are part of the first index.

    Does that make any sense? How can that be helpful, couldn't the db simply use the first index to get the performance benefit they are looking for? I'm puzzled by this but don't want to object if there is actually a good reason for doing this.
    It all depends on the order of the columns, and the position of the three columns of the proposed second index on the existing first one.

    Existing index: (ColA, ColB, ColC, ColD, ColE, ColF)

    Proposed index #1:
    (ColC, ColF, ColA) <== It makes sense to create the index

    Proposed index #2:
    (ColA, ColB, ColC) <== Most probably it doesn't make sense to create the index.
    Pablo (Paul) Berzukov

    Author of Understanding Database Administration available at amazon and other bookstores.

    Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

  3. #3
    Join Date
    May 2002
    Posts
    2,645
    If the columns are included in the existing index, the existing index is a covering set. It is possible that the optimizer will use the existing index via skip scan, but it also depends on the cardinality of the columns (e.g., fewer values up front, more values towards the end). How do the existing and proposed indexes compare in terms of leading columns? For example, if the first three of the existing match the order of the proposed, it's a no-brainer (waste of time to create the new index). If the proposed happens to be the last three columns of the existing, that is more likely to be a better candidate for a new index. If they are interspersed, test and see.

    In 11g, you can use invisible indexes within a session and see right then and there which is better. If you like the results, mark the index as visible.

  4. #4
    Join Date
    Nov 2011
    Posts
    2
    Extremely helpful replies. Thank you both very much.

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