IBM Mainframe Forum

Hi,

Consider Table-a is having col1,col2,col3,col4 as columns.
unique index is created on col3 and col4

select col1 from table-a
where col3 > :value:
and col4 = :value:

will the above query use the full index in the tablespace scan?

Please help me on this.

Thanks in advance

Regards,
Chandu

It will use all fields in the index to determine wether or not it needs to retrieve the data. but it will need to read all index entries starting from col3 > :value. (MatchCols = 1)
At least that is how it works unil now, IBM is constantly finding faster ways =>
On DB2 10 LUW (and maybe soon in DB2 z/OS) there is another type of accessmethod called "JUMP SCAN" where it will actually skip thru the index, landing on each col4 = :value.
http://justdb2chatter.blogspot.co.uk/20 ... 0-luw.html

Thanks GuyC