Is there a performance gain?

[Guest_imported]

Please consider the following case and let me know what you think:

T1 table with C1, C2, C3, C4, C5, C6 columns

Select *
From T1
Where C1 = a
And C2 = b
Order by C5, C6

Is there a performance gain of index (C1, C2, C3, C4, C5, C6) over index (C1, C2, C3, C4)?

 

[JimHorton]

YN,
Just off the top of my head, a bigger index would be slower access..and there's no advantage in your select by having those extra columns in the first index, as the Order by would be executed on the result set. Depending on the distribution, a full table scan might be faster still.
--Jim

 

[dbmsguy]

if this is under udb, you could use the db2 batch tool to find out this answer.

overall, however, your predictes are c1 and c2, so at most, that is all you need in the index. you are doing an order by which would have nothing to do with indexing.

the most approprate index would be c1, c2 and maybe even c2, c1 depending on distritbution. Paul

http://www.dbmsguy.com