Why isn't ALLOW REVERSE SCANS the default?
Why do we have to
- drop PK
- create an index
- recreate PK
What are the advantages of indexes that do not allow reverse scans?

ford_desperado@yahoo.com wrote:
> Why isn't ALLOW REVERSE SCANS the default?
> Why do we have to
> - drop PK
> - create an index
> - recreate PK
> What are the advantages of indexes that do not allow reverse scans?
>
What a great idea... may be it will in a "future release" ;-)

Cheers
Serge

ford_desperado@yahoo.com wrote:
> Why isn't ALLOW REVERSE SCANS the default?
> Why do we have to
> - drop PK
> - create an index
> - recreate PK
> What are the advantages of indexes that do not allow reverse scans?
>

This is legacy, i.e. index creation did (does) not allow reverse
scans... This feature was added at some point (I think 5.2), but
was not the default likely because of the extra overhead required
for reverse scans (i.e. each page must store a pointer to the previous
page as well and the next page in the index).

On 2004-12-13, Serge Rielau scribbled:

>ford_desperado@yahoo.com wrote:
>> Why isn't ALLOW REVERSE SCANS the default?
>> Why do we have to
>> - drop PK
>> - create an index
>> - recreate PK
>> What are the advantages of indexes that do not allow reverse scans?
>>
>What a great idea... may be it will in a "future release" ;-)

From the DB2 Administration Performance Guide:

"At the leaf node level there *can* be previous leaf pointers. This can
be of great benefit since once finding a particular key value in the
index by traversing the tree, the Index Manager can scan through the
leaf nodes in either direction to retrieve a range of values. This
ability to scan in either direction is only possible if the index was
created using the ALLOW REVERSE SCANS parameter."

I note that it says "can be" as opposed to "are". I may be
misinterpreting this, but I assume this means that these previous leaf
pointers do not exist if ALLOW REVERSE SCANS is not used? Presumably
that would make the index slightly smaller on disk, and therefore
slightly faster to access / update.

That said, I suspect any performance hit is either non-existant or
negligable. Otherwise, I would expect to find some warning about
potentially lower performance when using ALLOW REVERSE SCANS somewhere
in the manuals (and I can't).

In conclusion: you've got my vote for the suggestion!

In the meantime, it is possible to create an index for a primary key
which has ALLOW REVERSE SCANS without dropping the PK by pre-defining
an index for the primary key at table creation time. For example:

CREATE TABLE COUNTRIES (
ISO_CODE CHAR(2) NOT NULL,
NAME VARCHAR(64) NOT NULL
);

CREATE UNIQUE INDEX COUNTRIES_PK
ON COUNTRIES (ISO_CODE)
ALLOW REVERSE SCANS;

ALTER TABLE COUNTRIES
ADD CONSTRAINT PK PRIMARY KEY (ISO_CODE);

In other words: create the table without a primary key, define a unique
index with ALLOW REVERSE SCANS to serve as the primary key's index,
then use an ALTER TABLE statement to create the actual primary key
constraint. DB2 will notice that a unique index with the same columns
as the primary key constraint already exists, and will issue a warning
message that it is using an existing index to implement the primary key
(I'm not sure why a warning is issued as it's perfectly harmless and
what I intended to do all along!)

Anyway, the other advantage of this approach is that you can include
all sorts of other options in the index definition. I commonly used
INCLUDE (to take advantage of index-only access) and sometimes CLUSTER
in this way, for example:

CREATE UNIQUE INDEX COUNTRIES_PK
ON COUNTRIES (ISO_CODE)
INCLUDE (NAME)
ALLOW REVERSE SCANS;

The only downside to all this is that your SQL becomes rather more DB2
specific, though that may not be a concern depending on your situation.

HTH, Dave.
--
Cogito cogito ergo cogito sum
-- Ambrose Bierce

Serge, let me re-word the original poster's question:

Are there any advantages of using "indexes that do not allow reverse
scans?"

Dave,

"I note that it says "can be" as opposed to "are". I may be
misinterpreting this, but I assume this means that these previous leaf
pointers do not exist if ALLOW REVERSE SCANS is not used"

you can see for yourself, just compare execution plans for

SELECT MIN (ISO_CODE) FROM COUNTRIES
@
SELECT MAX (ISO_CODE) FROM COUNTRIES
@

do you see the difference?

On 2004-12-14, ak_tiredofspam@yahoo.com scribbled:

>Dave,
>
>"I note that it says "can be" as opposed to "are". I may be
>misinterpreting this, but I assume this means that these previous leaf
>pointers do not exist if ALLOW REVERSE SCANS is not used"
>
>you can see for yourself, just compare execution plans for
>
>SELECT MIN (ISO_CODE) FROM COUNTRIES
>@
>SELECT MAX (ISO_CODE) FROM COUNTRIES
>@
>
>do you see the difference?

I see that, when the index has ALLOW REVERSE SCANS, a reverse scan is
used on the second query. I also see that, when not using ALLOW REVERSE
SCANS, the access plan does not use a reverse scan (because it can't)
but uses a forward index scan in the case of both queries. But then,
this is pretty much what I'd expect to see.

An index scan in either direction is sufficient to answer both queries
(only one value is required for the answer in each case, and it doesn't
matter which direction we scan the index to obtain it).

However, when comparing the access plans for:

SELECT ISO_CODE FROM COUNTRIES ORDER BY ISO_CODE ASC

SELECT ISO_CODE FROM COUNTRIES ORDER BY ISO_CODE DESC

there is a significant difference between using ALLOW REVERSE SCANS and
not. When not using ALLOW REVERSE SCANS on the index, the optimizer
still uses an index scan on the second query, but generates a temporary
table from the result of that scan, then performs a reverse table-scan
on that temporary table.

Anyway, in terms of your re-wording of the OP's question:

>Serge, let me re-word the original poster's question:
>
>Are there any advantages of using "indexes that do not allow reverse
>scans?"

I think the technical questions related to this (which I was attempting
to raise with my comment about the note in the manual -- sorry I was a
bit vague about that :-) are as follows:

1. Is the space for previous leaf pointers *always* reserved in indexes
regardless of whether ALLOW REVERSE SCANS is used (in which case it
would make no difference to the physical size of the index on disk)?

2. Is maintenance of the previous leaf pointers a significant cost? In
other words, when a new index leaf is committed, the database now has
to fill in two pointer fields, one to the next leaf, one to the prior.
Does this significantly impact the amount of work involved in
maintaining the index?

I think the second question is the important one. I suspect the answer
is "No", because otherwise I would expect to see warnings in the
documentation about indexes with ALLOW REVERSE SCANS taking longer to
update/maintain.

Any thoughts or ideas about how to find out? Maybe a test script which
times an import of a few million rows into tables with and without
ALLOW REVERSE SCANS on their indexes?

Dave.
--
Cogito cogito ergo cogito sum
-- Ambrose Bierce

>An index scan in either direction is sufficient to answer both queries
>(only one value is required for the answer in each case, and it
doesn't
>matter which direction we scan the index to obtain it).

if you SELECT MAX(PK) FROM SALES rather then from COUNTRIES, and SALES
has 10,000,000 rows, it does matter very much - traversing the whole
index will take considerable time

ak_tiredofspam@yahoo.com wrote:
> Serge, let me re-word the original poster's question:
>
> Are there any advantages of using "indexes that do not allow reverse
> scans?"
>
I'm told that there is a potential for deadlocks if a update or delete
operations scan the index in the opposite direction as a competing
operation which keeps anything stronger than shares row locks.
To counteract that the optimizer favors forward scans.
Apparently the memory cost of reverse indexes is not considered
significant enough to matter.

Cheers
Serge

On 2004-12-14, ak_tiredofspam@yahoo.com scribbled:

>>An index scan in either direction is sufficient to answer both
>>queries (only one value is required for the answer in each case,
>>and it
>doesn't
>>matter which direction we scan the index to obtain it).
>
>if you SELECT MAX(PK) FROM SALES rather then from COUNTRIES, and SALES
>has 10,000,000 rows, it does matter very much - traversing the whole
>index will take considerable time

Yes, that's true. The access plan wouldn't (shouldn't?) differ though
the time taken to execute it most certainly would. So, we both agree
there are considerable advantages to using ALLOW REVERSE SCANS. To the
other side of the question, are there any disadvantages?

Personally, I use ALLOW REVERSE SCANS on every index in my databases,
under the assumption that the benefits to the optimizer far outweigh
any performance hit from the extra maintenance (I assume) they require.
Though in the absence of any hard data regarding such maintenance, this
is more of a guess on my part. Any thoughts?


Dave.
--
Cogito cogito ergo cogito sum
-- Ambrose Bierce