Results 1 to 3 of 3
  1. #1
    Join Date
    Aug 2005
    Posts
    5

    Unanswered: Allow reverse scan of primary & unique key indexes

    Dear all,

    My experience with DB2 is very limited.
    Today I was surprised to discover the default only possible index scan is from left to right, as such this one runs fast, using the primary key index

    db2 => select c_i_idf from du.dossier_uniek order by c_i_idf asc fetch first 10
    rows only

    However, this one runs extremely slow, an FTS with sort is shown in the explain plan

    db2 => select c_i_idf from du.dossier_uniek order by c_i_idf desc fetch first 10 rows only

    As a test I decided to drop the primary key on c_i_idf
    I added as a first step an unique index on c_i_idf
    Then I altered the table, recreated the primary key on c_i_idf
    Then runstats

    The command

    db2 => select c_i_idf from du.dossier_uniek order by c_i_idf desc fetch first 10 rows only

    runs as fast as the query with the order by c_i_idf asc attribute

    My questions are

    1. Why is DB2 not allowing the reverse scans by default ?
    2. What is the difference in index structure between a not allow reverse scan index and an allow reverse scan index?

    Regards and thankx in advance for your valuable advice
    Guy

  2. #2
    Join Date
    Mar 2003
    Posts
    343
    I agree with you. They should have made it default in the first place. Be sure to create the index with allow reverse scans specified.

    Structurally I believe that the page which contains the datavalue is where there are pointers one way (asc or desc) or both ways. When we first decided to use this I asked what the overhead or increase in size would be like and I was told that we would not notice it.

  3. #3
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    I haven't noticed any disk space increase with REVERSE SCANS ... Insert Performance is not impacted either ...
    I do not have a clue why REVERSE SCANs has not been made the default yet
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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