|
-
 Originally Posted by OldTimer
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.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|
Reply With Quote