DB2 9.1 for Linux

My question is related to Understanding DB2 Query Access Plans :: Chapter 6. The DB2 Optimizer :: Advanced dba certification guide and reference :: Misc :: eTutorials.org


Recognizing List Prefetch example:

Why is it necessary to do SORT and RIDSCN after IXSCAN? Aren't the keys returned by IXSCAN already sorted?

Does it make any difference if index TPCDL_SUMMARY2_IDX is a clustering index?



Unrelated question: Is I create an index on a VARCHAR column, can DB2 use it?

Keys are, but RIDs are not, and you want them in sequence for efficient prefetch.
Yes, it might eliminate the sort (or change the access plan altogether).

Why not?

list prefech is used to fetch non contiguous data more efficient。It actually sorted data by physical page number not by index key ,That is why sort operator is needed。
This fetch method can avoid data repage which will happen in the synchronize fetch when index clustor is low。。。。。