Multiply indexes for one query.

Larret Miyuki · New User Joined: 07 Apr 2010 Posts: 7 Location: Poland

Does DB2 for z/OS use multiply indexes for query, or only one that is probably most efficient. If DB2 uses only one index, is there a way to use multiply?

sushanth bobby · Posted: Thu Jul 01, 2010 4:21 pm

Hi Larret, Welcome to IBMMAINFRAMES.

Answer is "it depends" on the optimizer and the available statistics.

In General,
Multiple Column Indexes are efficient for equality. And multiple Indexes are more efficient on comparisons.

Sushanth

GuyC · Posted: Thu Jul 01, 2010 4:36 pm

The answer to this single line triple-question can fill a redbook.
the answers are : sometimes sometimes sometimes.

Anuj Dhawan · Posted: Thu Jul 01, 2010 5:48 pm

The main purposes of indexes are:

Anuj Dhawan · Posted: Thu Jul 01, 2010 5:57 pm

I'll start with,probably, what you want to listen - in a way, adding indexes to a table slows down insert and updates to that table, why? Because, each of these changes have to be applied not only to the actual data but the index pages also. And it will surely (should be acceptable, right?) eats up more disk space. If your database is small and your tables have few indexes on them, then adding a new index, which might not be required at all though, will probably not be noticed on modern fast machines. If your data is huge, very huge, your table is heavily indexed or you are runing on old machnines (actuators are not that good) then sometimes you have to make intelligent choices about which indexes are the most important to the performance of your program. The indexes for seldom-used queries sometimes have to be removed for the sake of more important indexes and the overall performance of updates to the table.

Because action queries on a table are affected by the number of indexes that table has, a table that is updated constantly by multiple users may not be able to have as many indexes as one that stays very static - so yes, as others have siad - yep, it depends.