IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

Splitting INDEX - Can it improve performance


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Mar 02, 2009 11:49 am
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Mar 02, 2009 11:50 am
Reply with quote

Splitting for reasons like FULL MATCH INDEX ACCESS
Back to top
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Mar 02, 2009 2:43 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Mar 02, 2009 2:58 pm
Reply with quote

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
View user's profile Send private message
ashimer

Active Member


Joined: 13 Feb 2004
Posts: 551
Location: Bangalore

PostPosted: Mon Mar 02, 2009 3:05 pm
Reply with quote

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
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Mon Mar 02, 2009 3:09 pm
Reply with quote

Oh Yes.
Thank You Very Much ASHIMER for clarifying
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts exploiting Z16 performance PL/I & Assembler 2
No new posts Cobol file using index COBOL Programming 2
No new posts JOIN STATEMENT PERFORMANCE. DFSORT/ICETOOL 12
No new posts Splitting group records based on deta... DFSORT/ICETOOL 8
No new posts DL/I status code AK for GU call using... IMS DB/DC 1
Search our Forums:

Back to Top