View previous topic :: View next topic
|
Author |
Message |
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Hi,
I have an index with 3 columns, but most of the select query for the table uses only 2 columns of this index.
Splitting this index into 2 can it by any chance improve performance like,
Currently = One index with 3 columns
After Splitting = 2 indexes
first_index = 2 columns
second index = 1 columns.
Thank You,
Sushanth Bobby |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Splitting for reasons like FULL MATCH INDEX ACCESS |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
No it wont improve performance ..... say your first index has 3 cols A,B,C and your select uses A and B ... the matchcols will be 2 depending on your where clause ... the same is the case if you split it into two ... but for the later maintaing 2 index spaces overhead is there ... |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Ashimer,
Yes, overhead is there for maintaining the 2nd index. I AGREE. But the doubt i got is, for the first index, i will get a FULL INDEX MATCH. Does it by any chance improve performance of the query.
Thank You,
Sushanth |
|
Back to top |
|
|
ashimer
Active Member
Joined: 13 Feb 2004 Posts: 551 Location: Bangalore
|
|
|
|
There are only two types of index scans: matching index scans and non-matching index scan. whether you split your index or not the matching cols is always 2 for your query ... which leads to the same result ... |
|
Back to top |
|
|
sushanth bobby
Senior Member
Joined: 29 Jul 2008 Posts: 1020 Location: India
|
|
|
|
Oh Yes.
Thank You Very Much ASHIMER for clarifying |
|
Back to top |
|
|
|