Does anyone know if there is any overhead associated with using the reverse scan option on indexes?

The perf manual says that the a leaf page contains references to both the page before it and the page following it - does not seem like much overhead considering the benefits that it can give. However, just wondering how much more space that could end up occupying. Will appreciate if someone can share their experience.

udb eee 7.2 fp 7 on aix 433 ml 11

Try this:

1. Create index without reverse scan.
Sample:
CREATE INDEX schema.index_name ON schema.table_name (col1 asc, col2 asc);

2. reorgchk on schema.index_name
Look for ISIZE parameter with is "index size".

3. drop index and create index with reverse scans:
Sample:
CREATE INDEX schema.index_name ON schema.table_name (col1 asc, col2 asc) ALLOW REVERSE SCANS;

4. reorgchk on schema.index_name
Look for ISIZE parameter with is "index size".

5. Compere the ISIZE parameter of step 2 and step 4.

Hope this helps,
Grofaty

I think there is more than space considerations involved. I think there are things that need to get updated when index entries are added (and sometimes changed), especially at the page level. So that involves some CPU, and possibly bufferpool storage and disk access.

I think a lot of people have wondered the exact same thing, but I have assumed it must not be trivial or else it would be the default. But that is just a wild guess.

Thank you for your inputs - space is a big consideration at this time, since we're running low on it. But I must admit that Marcus has made some good observations about index page splits which I have concerns with as well. Especially if this is applied to a clustering index.