为什么不允许REVERSE SCANS默认？ [英] Why isn't ALLOW REVERSE SCANS the default?
问题描述
为什么不允许REVERSE SCANS默认？
为什么我们必须
- 掉落PK
- 创建索引
- 重新创建PK
不允许反向扫描的索引的优点是什么？
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?
推荐答案
fo************@yahoo.com 写道：
为什么不允许REVERSE SCANS默认？
为什么我们必须
- 删除PK
- 创建索引
- 重新创建PK
不允许反向扫描的索引的优点是什么？
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?
多么好主意...可能会在未来发布 ;-)
干杯
Serge
What a great idea... may be it will in a "future release" ;-)
Cheers
Serge
fo ************ @ yahoo.com 写道：
为什么不是'是否允许反向扫描默认？
为什么我们必须
- 删除PK
- 创建索引
- 重新创建PK
索引的优点是什么？不允许反向扫描？
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?
这是遗留的，即索引创建确实（确实）不允许反转
扫描...此功能在某些时候添加（我认为是5.2），但
不是默认的，因为需要额外的开销
用于反向扫描（即每个页面必须存储指针到之前的
页面以及索引的下一页。
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).
2004-12-13，Serge Rielau潦草地写道：
On 2004-12-13, Serge Rielau scribbled:
fo ************ @ yahoo.com写道：
fo************@yahoo.com wrote:
为什么不允许REVERSE SC ANS是默认的吗？
为什么我们必须
- 删除PK
- 创建索引
- 重新创建PK
不允许反向的索引的优点是什么扫描？
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" ;-)
来自DB2 Administration Performance Guide：
"在叶子节点级别，*可以*是以前的叶子指针。这可以带来很大的好处，因为一旦通过遍历树在
索引中找到特定的键值，索引管理器就可以扫描
叶子节点在任一方向上检索一系列值。只有当使用ALLOW REVERSE SCANS参数创建索引
时，才能在任一方向扫描这个
的能力。
我注意到它说可以而不是是。我可能会错误解释这个，但我认为这意味着如果不使用ALLOW REVERSE SCANS，这些前面的叶子
指针不存在？据推测
会使索引在磁盘上略小，因此
访问/更新稍快一点。
表示，我怀疑任何表现都不会出现或者可以忽略不计。否则，在手册中使用ALLOW REVERSE SCANS
时，我希望能找到一些关于
可能会降低性能的警告（我不能）。
结论：你对我的投票赞不绝口了！
同时，可以为主键创建一个索引
通过预先定义
创建表时主键的索引，可以在不丢弃PK的情况下允许REVERSE SCANS。例如：
CREATE TABLE COUNTRIES（
ISO_CODE CHAR（2）NOT NULL，
NAME VARCHAR（64）NOT NULL
）;
创建独特的指数国家_PK
ON国家（ISO_CODE）
允许反转扫描;
ALTER TABLE COUNTRIES
ADD CONSTRAINT PK PRIMARY KEY（ISO_CODE）;
换句话说：创建没有主键的表，定义一个唯一的
索引，ALLOW REVERSE SCANS作为主键的索引，
然后使用ALTER TABLE语句创建实际的主键
约束。 DB2会注意到具有相同列的唯一索引
作为主键约束已经存在，并将发出警告
消息它正在使用现有索引来实现主键
（我不知道为什么会发出警告，因为它是完全无害的，而且我一直打算做什么！） />
无论如何，这种方法的另一个优点是你可以在索引定义中包含
各种其他选项。我常用
INCLUDE（以利用仅索引访问权限），有时以这种方式使用CLUSTER
，例如：
CREATE UNIQUE INDEX COUNTRIES_PK
ON COUNTRIES（ISO_CODE）
INCLUDE（NAME）
允许反转扫描;
所有这一切的唯一缺点是你的SQL变得更具特定的DB2
，但根据你的情况，这可能不是一个问题。
HTH，Dave。
-
Cogito cogito ergo cogito sum
- Ambrose Bierce
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
这篇关于为什么不允许REVERSE SCANS默认？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
