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

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.

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