Query on Usage of Unique Index



IBM's flagship relational database management system

Query on Usage of Unique Index

Postby chandurokzz » Fri Jul 13, 2012 6:37 pm

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
chandurokzz
 
Posts: 10
Joined: Wed Apr 11, 2012 12:49 pm
Has thanked: 1 time
Been thanked: 0 time

Re: Query on Usage of Unique Index

Postby GuyC » Fri Jul 13, 2012 7:09 pm

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
I can explain it to you, but i can not understand it for you.
GuyC
 
Posts: 315
Joined: Tue Aug 11, 2009 3:23 pm
Has thanked: 1 time
Been thanked: 4 times

Re: Query on Usage of Unique Index

Postby chandurokzz » Mon Jul 16, 2012 9:35 am

Thanks GuyC :)
chandurokzz
 
Posts: 10
Joined: Wed Apr 11, 2012 12:49 pm
Has thanked: 1 time
Been thanked: 0 time


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post