Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2003
    Posts
    343

    Question Unanswered: Index reverse scans

    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

  2. #2
    Join Date
    Jan 2003
    Posts
    1,605
    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

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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.

  4. #4
    Join Date
    Mar 2003
    Posts
    343
    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.

Posting Permissions

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