On 2004-12-13, Serge Rielau scribbled:
fo************@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