DB2 索引整理

分类： DB2 2012-09-21 18:22  40人阅读  评论(0)  收藏  举报

1、创建集群索引
CREATE INDEX INX_NAME ON TABLE_NAME (COL_NAME) CLUSTER
为了让语句更有效，可以通过ALTER TABLE语句相关的PCTFREE参数来使用集群索引，以便于可以将新数据插入到正确的页上，从而维护该群集的次序。通常情况下，表上的INSERT操作越多，为维护集群所需要的PCTFREE值就越大。因为这个索引确定数据在屋里页上放置的次序，所以对任何特定的表都只能定义一个集群索引。
另一方面，如果这些新行的索引关键字值总是新的大关键字值，那么表的集群属性将尝试把它们放到表的末尾。其他页上有空闲空间对保持集群没有什么作用。在这种情况下，将表设置为追加方式可能优于使用集群索引，改变表来拥有一个大的PCTFREE值。可以使用如下命令将表设置为追加方式：ALTER TABLE TBL_NAME APPEND ON.


2、创建双向索引
使用CREATE INDEX语句中的ALLOW REVERSE SCANS参数创建的单索引可以向左或向右扫描，即双向索引。


3、索引页合并与分裂
CREATE INDEX语句的MINPCTUSED子句指定在索引叶页上最小已用空间的阈值。如使用该子句，可以对这个索引启用联机索引重组。一旦启用了联机索引重组，就可以参照一下参考事项来确定是有执行联机重组：当从这个索引的一个叶页中删除一个关键字后，并且该页上已用空间的百分比小于所指定的阈值，那么就检查相邻的索引页来确定是否可以将两个叶页上的关键字合并到单个索引页中。
如：CREATE INDEX IDX_NAME ON TAB_NAME (COL_NAME) MINPCTUSED 20
当从这个索引删除一个关键字时，如果这个索引页上的其余关键字占用索引页上20%或更小的空间，就可以尝试将这个索引页的关键字与相邻索引页的关键字合并，来删除这个索引页。如果组合的关键字可以全部位于一页上，就执行这个合并并删除其中一个索引页。

CREATE INDEX 语句的PCTFREE子句指定创建索引时，每个索引页中要留作空闲空间的百分比。在索引页上保留更多的空闲空间将导致更小的页分割，这将减少为重新获得顺序索引页面而重组表的需要，从而增加预存取，而预存取是一个可以提高性能的重要部件。此外，如果总是存在大关键字值，那么就要考虑降低CREATE INDEX语句的PCTFREE子句的值。
对于只读表上的索引，使PCTFREE为0；对于其他索引，使PCTFREE为10（DB2建表时，PCTFREE默认为10），以提供可用的空间，从而加快插入操作的速度。此外，对于有集群索引的表，这个值应该更大一些，以确保集群索引不会被分成太多的碎片。如果存在大量的插入操作，那么使用15到35之间的值或许会更合适一些。


4、完全索引访问（index access only）
CREATE INDEX语句的INCLUDE子句指定在创建索引时，可以选择包含附加的列数据，这些附加的列数据将与键存储在一起，但实际上它们不是键自身的一部分，所以不被排序。在索引中包含附加列的主要原因是为了提高某些查询的性能。DB2将不需要访问数据页，因为索引页早已经提供了数据值。只可以为包含的列定义唯一索引。但在强制执行索引的唯一性时不考虑被包含的列。
假设需经常获得按ID排序的员工列表，如下：
SELECT ID, NAME FROM EMP ORDER BY ID;
可以创建如下索引：
CREATE INDEX IDX_EMP_ID ON EMP (ID) INCLUDE(NAME);
结果，查询结果所需的所有数据都显示在索引中，不需要检索数据页。那么，为什么不干脆在索引中包括所有的数据呢？首先，这需要数据库中的更多物理空间，因为本质上数据是在索引中复制的。其次，只要更新了数据的值，数据的所有副本都需要更新，在发生许多次更新的数据库中，开销相当大。


5、创建索引的相关问题
a、如果能在一个合理的时间内结束查询，应避免添加索引，因为索引会降低更新操作的速度并消耗额外的空间；
b、基数较大的列很适合用来做索引；
c、考虑到管理上的开销，尽量避免在索引中使用多于5个的列；
d、对于多列索引，将查询中引用最多的列放在定义的签名；
e、避免添加与已有索引相似的索引。如，已在一个表的(c1, c2)上有一个索引index1, 但又创建一个(c2)的索引index2。这样，索引index2并没有添加任何东西，它只是index1的冗余，现在反而成了额外的开销。
f、如果表是只读的，并且包含很多的行，那么可以尝试定义一个索引，通过CREATE INDEX中的INCLUDE子句使该索引包含查询中引用的所有列（被INCLUDE子句包含的列并不是索引的一部分，而只是作为索引页的一部分来存储，以避免附加的数据）；
g、对于联机事务处理（OLTP）环境，创建一个或两个索引；对于只读查询环境，可以创建5个以上索引；对于混合查询和OLTP环境，可以创建2到5个索引；
h、要提高对父表执行删除和更新操作的性能，在外键上创建关系索引。