CODEWITHSUNDEEP
indexingdb2
Forums Data
Forums Data

Reputation: 1

multiple indexes same column sql

For DB2 database, Consider Table tbl (colA, colB, colC) and queries as

select *
 from tbl
where tbl.colA = 1234.

select *
from tbl
where tbl.colA =1234
and tbl.colB = 73874

Will it help if we create two indexes i) on colA ii) composite - colA,colB

if the above sql's are frequently accessed then will it help to have two indexes as above. Will the optemizer pick the correct index based on the query.

Upvotes: 0

Views: 291

Answers (1)

Barmar
Barmar

Reputation: 782785

You only need index (ii). A composite index is used whenever the search data is in a prefix of the columns. So an index on colA,colB,colC,colD will be used when you search colA, colA and colB, colA, colB, and colC, and colA, colB, colC, and colD.

Upvotes: 2

Related Questions