DB2 Indexing



IBM's flagship relational database management system

DB2 Indexing

Postby maragatham » Fri Nov 12, 2010 5:36 pm

Hi,

I am asked to improve the performance of 2 individual queries.
First query searches on column A of Table1. Column A has unique values.
Second query searches on column B of Table1. Column B is not unique.
Obviously a combination of COL A and COL B is unique.

And I found that there are no indexes defined on these columns.
Will an index on (COL A, COL B) improve performance on these individual queries??

Please let me know if it is OK for me to raise a request to create an index on Table1 on (COL A, COL B)?
maragatham
 
Posts: 9
Joined: Tue Nov 09, 2010 3:08 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Indexing

 

Re: DB2 Indexing

Postby alexm » Fri Nov 12, 2010 9:50 pm

Basically, indexes were introduced to help optimizing performance ;) .
Whether you should request new indexes on the questionable table(s) is depending on many other facts, such as: how often are these "individual" queries performed, what's the size of the objects, what's the update frequency onto the tables (DB2 needs to maintain the indexes, and because of this, performance degradation on updaters may occur), etc. etc.

You might want consider to speak to your database designers, performance group, or whoever else cares about these things in your shop.
User avatar
alexm
 
Posts: 33
Joined: Wed Oct 13, 2010 6:40 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Indexing

Postby RENGANATHAN » Sat Nov 27, 2010 6:33 pm

let thnk abt the table size,f the table size is small index will not help in perfomnce,if t is big ,create a index for that row and the table will go for index scan only ,performance will be gud
learner(admin)
RENGANATHAN
 
Posts: 9
Joined: Thu Jul 29, 2010 2:23 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Indexing

Postby enrico-sorichetti » Sat Nov 27, 2010 6:42 pm

this is not an sms or a chat line...
please, please,
have Your keyboard fixed or type more slowly or learn how to express Yourself properly.
Thank You!
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2648
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 132 times

Re: DB2 Indexing

Postby RENGANATHAN » Sat Nov 27, 2010 6:59 pm

hey brother

if the table having 100 rows means index scan will not help to improve the performance,so db2 will go for tablescan.

if ur table having nearly thousands of record means index scan will improve the performance...

so decide to create the index depend upon your table size.
learner(admin)
RENGANATHAN
 
Posts: 9
Joined: Thu Jul 29, 2010 2:23 pm
Has thanked: 0 time
Been thanked: 0 time

Re: DB2 Indexing

Postby dick scherrer » Sun Nov 28, 2010 9:26 am

Hello,

Enrico is not your brother. . .

You need to make sure that you post replies that are appropriate rather than chatroom slang/abbreviations.

You also need to make sure your reply provides an answer/solution to the question asked. Your replies some other topics have been deleted for this reason.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to DB2