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 Order BY AND Index Nileshkul DB2 2 Sat Dec 31, 2016 6:33 pm
No new posts BC, BCR, BRC, BRCL performance steve-myers PL/I & Assembler 0 Fri Dec 23, 2016 7:44 am
No new posts PL/I code tuning/Performance improvement Virendra Shambharkar PL/I & Assembler 4 Mon Dec 05, 2016 11:57 am
No new posts What are the way we can improve CPU p... Gunapala CN DB2 10 Mon Oct 24, 2016 2:16 pm
No new posts Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm


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