Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Splitting INDEX - Can it improve performance

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1013
Location: India

PostPosted: Mon Mar 02, 2009 11:49 am    Post subject: Splitting INDEX - Can it improve performance
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: 1013
Location: India

PostPosted: Mon Mar 02, 2009 11:50 am    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Mon Mar 02, 2009 2:58 pm    Post subject:
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    Post subject:
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: 1013
Location: India

PostPosted: Mon Mar 02, 2009 3:09 pm    Post subject:
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am
No new posts CONTIG for performance? JPVRoff JCL & VSAM 7 Fri Jun 09, 2017 8:39 am
No new posts Splitting of single report to seperat... abdulrafi TSO/ISPF 7 Mon Mar 27, 2017 3:59 pm
No new posts splitting a file abdulrafi DFSORT/ICETOOL 4 Fri Mar 24, 2017 11:51 am
No new posts setting new limit Key values for inde... srilata83 DB2 1 Fri Feb 10, 2017 9:24 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us