Is this redudant index?

grofaty · #1 (**permalink**) 10-25-07, 05:32

Hi,
on my system DB2 v8 fp9 on Windows XP SP2 I run db2advis command for one particular SQL.

The output recommending indexes was:

Code:

CREATE INDEX ADMIN.IDX710250925460000 ON ADMIN.TAB1 
(COL1 ASC, COL2 ASC, COL3 ASC) ALLOW REVERSE SCANS ;

CREATE INDEX ADMIN.IDX710250926010000 ON ADMIN.TAB1
(COL1 ASC, COL2 ASC, COL3 ASC, COL4 ASC) ALLOW REVERSE SCANS ;

This output surprised me. Isn't first index redundant?

Thanks,
Grofaty

guyprzytula · #2 (**permalink**) 10-25-07, 06:55

yes, according my opinion first index is included in second

stolze · #3 (**permalink**) 10-25-07, 07:41

Right, it is included.

However, the 2nd index may be bigger because index keys are longer and less keys may fit in an index page, requiring more pages. More pages may lead to a higher index tree. Thus, it could be beneficial to have both indexes - depending on what you do with the database.

Marcus_A · #4 (**permalink**) 10-25-07, 10:17

IMO db2advis sucks. I have seen it generate indexes with descending sequence on columns for absolutely no good reason.

grofaty · #5 (**permalink**) 10-26-07, 02:15

Hi,
thanks for your comments. I have made an explain and with two indexes explain was better in third decimal. I have executed SQL with two indexes and with only with second one and the execution time was the same (differences is about a second). So I dropped first index.

Marcus_A, I have seen some strange suggestions from db2advis too. But sometimes I like to see second opinion to get some extra idea. Specially if there is a complex SQL to handle.

Thanks,
Grofaty

Internet Services

Web Development

Digital Marketing

Consumer Tech