Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2009
    Posts
    66

    Unanswered: about list prefetch

    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?

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by L_DBA_L View Post
    Why is it necessary to do SORT and RIDSCN after IXSCAN? Aren't the keys returned by IXSCAN already sorted?
    Keys are, but RIDs are not, and you want them in sequence for efficient prefetch.
    Quote Originally Posted by L_DBA_L View Post
    Does it make any difference if index TPCDL_SUMMARY2_IDX is a clustering index?
    Yes, it might eliminate the sort (or change the access plan altogether).

    Quote Originally Posted by L_DBA_L View Post
    Unrelated question: Is I create an index on a VARCHAR column, can DB2 use it?
    Why not?

  3. #3
    Join Date
    Nov 2011
    Posts
    334
    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。。。。。

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •