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
 

 

Composite Index

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

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Tue Jan 11, 2011 11:06 am    Post subject: Composite Index
Reply with quote

Hi,

I have a doubt.

Lets say I have a table name TABLEA having 5 columns ( col_a, col_b, col_c, col_d, col_e).

I have a query like below:

SELECT * from TABLEA
where col_a = something
and col_b = something
and col_c = something

For a scenario like this,
What kind of index would give me better query performance ?
1. A single index as col_a, col_b and col_c as a composite non-unique index.
OR
2. Three indexes as col_a as a non-unique index, col_b as a non-unique index, col_c as a non-unique index.

I have an understand that a single index with the three columns would give a better query performance but would like to have a confirmation from the experts.

Thanks in advance.

Rakesh.
Back to top
View user's profile Send private message

dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 11, 2011 11:27 am    Post subject:
Reply with quote

Hello,

I'd use the composite index, but i'd also ensure the definition placed the most unique column first, the next most unique column second, and the least unique column third.

For example if the columns are for country, region, and custid, i'd define this as custid, then region, then country.
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Tue Jan 11, 2011 1:21 pm    Post subject: Reply to: Composite Index
Reply with quote

Thank You for confirming this.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Tue Jan 11, 2011 1:36 pm    Post subject:
Reply with quote

dick scherrer wrote:
Hello,

I'd use the composite index, but i'd also ensure the definition placed the most unique column first, the next most unique column second, and the least unique column third.

For example if the columns are for country, region, and custid, i'd define this as custid, then region, then country.

In my experience most reports on this kind of tables are sorted on country/region/custid.
and many (less frequent ?) queries only supply the country or country/region.
The performance gain for a query that supplies all 3 values with an index custid,region,country opposed to an index country/region/custid is in my experience very small .
especially considered the performance loss on queries with the sorts or queries that supply only country/region.

Indexes required also depends on R.I. so if you have R.I to country and to region and to custid then you might need all 3 indexes. or at least the index to the table where most deletes happen. which in this case would probably be customer.

So arguments for both cases. and as usual the final answer is : "it depends".
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Jan 11, 2011 8:45 pm    Post subject: Reply to: Composite Index
Reply with quote

Hi Guy,

Quote:
So arguments for both cases. and as usual the final answer is : "it depends".
Yup. . . icon_smile.gif

It was my understanding (possibly not correct/complete) that the reason for this key was for this particular query. If it will be used for more, more considertion would be in order.

As you say - "it depends".

d
Back to top
View user's profile Send private message
rakesh1155

New User


Joined: 21 Jan 2009
Posts: 84
Location: India

PostPosted: Wed Jan 12, 2011 8:54 am    Post subject: Reply to: Composite Index
Reply with quote

Well... I agree "It depends in most cases" icon_smile.gif

Thank You Guys!!
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 Several errors during building altern... Andi1982 JCL & VSAM 11 Wed Jul 06, 2016 7:39 pm
No new posts Drop building Alternate Index for a p... bhavin.mehta JCL & VSAM 6 Mon Jul 04, 2016 3:47 pm
No new posts VSAM Alternate Index in CICS jacobdng CICS 2 Fri May 06, 2016 1:41 pm
No new posts Rebuilding IMS secondary index withou... Hooman24 IMS DB/DC 6 Tue Mar 29, 2016 12:22 pm


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