DB2 的REORG_学习（4）_表和索引重组的分析

1.确定何时重组表和索引

  对表数据进行大量更改之后，在逻辑上连续的数据可能会存储在不连续的物理数据页中，在许多更新操作创建溢出(overflow)记录后尤其如此。按这种方式组织数据时，数据库管理器必须执行额外的读操作才能访问所需的数据。另外，在删除大量的行之后，空间没有释放（Deleted Record），也需要执行额外的读操作。

关于此任务

  表 reorg 操作会通过整理数据碎片 来 减少浪费的空间。并且，此操作还将对行进行重新排序以合并溢出记录，从而加快数据访问速度并最终提高查询性能。您还可以指定特定的索引来对数据进行重新排序，以便查询通过最少的I/O读取操作就可以访问数据。

  对表数据进行大量更改会导致索引更新（索引节点分裂，层数变多），并引起索引性能下降。索引叶子页可能会碎片化或者出现集群程度不佳的情况，并且索引所形成的层数可能会超出为了获得最佳性能而必需的层数（通常，几百万的数据的索引层次一般为3，正常生产环境中索引的层数很少超过4）。所有这些问题都会导致 I/O 增加以及性能下降。
  下列任何一个因素表示可重组表或索引：

• 自从上次重组表之后，已对该表执行大量的插入、更新和删除活动
• 使用集群率较高的索引的 查询 ，性能发生显著变化
• 在执行 RUNSTATS 命令以刷新统计信息后，性能未得到改善
• REORGCHK 命令的输出指示需要重组表或它的索引来提高性能
  在某些情况下，reorgchk工具可能会建议表重组，即使刚刚执行了一个表重组操作。你应该分析reorgchk工具的建议，然后评估 潜在的益处和执行重组的成本之间的平衡利益
• 如果主要考虑回收空间，那么可使用带 CLEANUP 和 RECLAIM EXTENTS 选项的 REORG 命令。
  ADMIN_GET_INDEX_INFO 和 ADMIN_GET_TAB_INFO 函数的 RECLAIMABLE_SPACE 输出 可以显示可回收的空间量（以千字节计）。如果在运行 ADMIN_GET_INDEX_INFO 和 ADMIN_GET_TAB_INFO 函数之前发出带 CLEANUP 选项的 REORG 命令，那么这些函数的输出显示可供回收的最大空间。使用此信息来确定 什么时候使用 带 RECLAIM EXTENTS 的 REORG 命令有助于降低表和索引的大小。

  REORGCHK 命令返回有关数据组织的统计信息，并且可以在是否需要重组特定表或索引这一问题上为您提供建议。如果仅考虑回收空间，那么 ADMIN_GET_INDEX_INFO 和 ADMIN_GET_TAB_INFO 函数的 RECLAIMABLE_SPACE 输出概述可供回收的空间量。但是，通过定期地或者在特定时间对 SYSSTAT 视图运行特定查询，可以构造一个历史记录，这有助于您确定可能对性能产生重大影响的趋势。

要确定是否需要重组表或索引，请查询 SYSSTAT 视图并监视下列统计信息：

行的溢出情况Overflow of rows

查询 SYSSTAT.TABLES 视图中的 OVERFLOW 列以监视溢出值。此值表示在原始页中放不下的行数。当可变长度列导致记录长度扩展到某个程度，以致于某一行在数据页上分配给它的位置中放不下时，该行的数据将溢出。更改表结构add column时，行长度也会更改。在这种情况下，在该行中的原始位置将保留一个指针，而数据实际存储在由该指针指示的另一位置。这可能会影响性能，因为数据库管理器必须根据该指针来查找该列的内容。这个包括两个步骤的过程增加了处理时间，并且还可能增加所需执行的 I/O 次数。重组表数据将消除任何行溢出情况。如果压缩了行，然后进行更新，那么可能会发生溢出。如果新行无法再压缩，或者新压缩的行变得更长都会发生此种溢出。

访存统计信息Fetch statistics

查询 SYSSTAT.INDEXES 目录视图中的以下3个列，以便确定按索引顺序访问表时预取程序的效率。这些统计信息体现对底层表执行预取程序时的平均性能特征。

• AVERAGE_SEQUENCE_FETCH_PAGES （平均顺序获得_页）列存储可以按表中顺序访问的平均页数。可以按顺序访问的页适合于预取。较小的数目表明预取程序未充分发挥作用，原因是它们无法读入由表空间的 PREFETCHSIZE（预取大小） 值指定的所有页数。较大的数目指示预取程序将有效地执行。对于集群索引和表，此数目应该接近 NPAGES（包含一些行的页数）的值。
• AVERAGE_RANDOM_FETCH_PAGES （平均随机获得_页）列存储使用索引来访存表行时，在两次顺序页访问之间访存的随机表页的平均数目。当大多数页按顺序时，预取程序忽略少量的随机页，并按已配置的预取大小继续预取。当表变得更加混乱时，随机访存页数就会增加。通常，由于在表的末尾或溢出页中发生了无序的插入，导致了这样的表的无组织。当使用索引来访问某个范围的值时，这将降低查询性能。
• AVERAGE_SEQUENCE_FETCH_GAP（平均顺序获得_间隙）列存储使用索引访存表行时表页序列之间的平均间隔。此间隔是通过扫描索引叶子页进行检测的，每个间隔都表示在各个表页序列之间必须随机访存的表页的平均数目。随机访问许多页时就会发生这种情况，这会中断预取程序。数目较大表明表是无组织的表或较差集群的索引。

包含已被标记为“已删除”但尚未被除去的记录标识 (RID) 的索引叶子页的数目 Number of index leaf pages containing record identifiers (RIDs) that are marked deleted but not yet removed

将 RID 标记为“已删除”时，通常不会以物理方式将其删除。这意味着，可用空间可能会被这些在逻辑上已被删除的 RID 占用。要检索其中每个 RID 都被标记为“已删除”的叶子页的数目，请查询 SYSSTAT.INDEXES 视图的 NUM_EMPTY_LEAFS 列。对于并非其中所有 RID 都被标记为“已删除”的叶子页，在逻辑上已被删除的 RID 的总数存储在 NUMRIDS_DELETED 列中。

使用此信息来估算通过调用带有 CLEANUP ALL 选项的 REORG INDEXES 命令可以回收的空间量。如果您只想回收其中所有 RID 都被标记为“已删除”的页中的空间，那么请调用带有 CLEANUP PAGES 选项的 REORG INDEXES 命令。

索引的集群比率和集群因子统计信息Cluster-ratio and cluster-factor statistics for indexes

通常，表只有一个索引可以具有较高的集群度。集群比率统计信息存储在 SYSCAT.INDEXES 目录视图的 CLUSTERRATIO 列中。此值介于 0 与 100 之间，它表示索引中的数据集群程度。如果收集详细的索引统计信息，那么在 CLUSTERFACTOR 列中将存储较详细的集群因子统计信息（介于 0 与 1 之间），并且 CLUSTERRATIO 的值为 -1。在这两种集群统计信息中，只有一种可以记录在 SYSCAT.INDEXES 目录视图中。要将 CLUSTERFACTOR 值与 CLUSTERRATIO 值进行比较，请将 CLUSTERFACTOR 值乘以 100 以获得一个百分比值。

如果查询用到的索引需要访存表中比较多的数据，而不是只访问索引或者只通过唯一索引访问表的一条记录，那么在具有较高聚合比率的情况下可能执行的更好。低的聚合度导致此类扫描要执行更多的I/O,因为在每个数据页经过第一次访问后，下次访问该页时，该页仍在缓冲池中的可能性减小。增大缓冲区大小也可以提高非聚合索引的性能。

如果表数据最初是根据某个索引进行聚集的，而集群统计信息指示现在很少为统一索引聚合数据，那么您可能想重组该表以再次聚合数据。而且，如果启用了智能数据预取，那么它可以改进低集群数据的性能，这会减少对表执行 REORG 命令的需要。智能数据预取通过每当存在低集群数据页时就从顺序检测预取切换至提前读预取来实现此目标。

索引叶子页的数目Number of leaf pages

请查询 SYSCAT.INDEXES 视图中的 NLEAF 列，以便确定索引所占用的叶子页的数目。此数目指示对索引进行完整扫描所需执行的索引页 I/O 次数。

理想情况下，索引所占用的空间量应该尽可能少，以便减少索引扫描所需执行的 I/O 次数。随机的更新活动会导致页分割，从而增大索引大小。在表 REORG 操作期间，可以使用最小空间量来构建每个索引重建每个索引。

缺省情况下，构建索引时，将在每个索引页中保留 10% 的可用空间。要增加可用空间量，请在创建索引时指定 PCTFREE 选项。每当重组索引时，都将使用指定的 PCTFREE 值。大于 10% 的可用空间值可以降低索引重组频率，这是因为，有额外的空间可以容纳所插入的附加索引内容。

空数据页的数目

要计算表中的空页数，请查询 SYSCAT.TABLES 视图中的 FPAGES 和 NPAGES 列，然后将 FPAGES 值（使用中的总页数）减去 NPAGES 值（包含行的页数）。空页可能是由于整个范围内的行被删除而产生。

随着空页数的增加，就需要进行表重组。重组表时，将回收空页并减少表所使用的空间量。另外，因为表扫描期间会将空页读入缓冲池，所以回收未使用的页可以提高扫描性能。

如果表的使用中的总页数 (FPAGES) 小于或等于 (NPARTITIONS * 1 个扩展数据块大小)，那么建议您不要进行表重组。对于分区表，NPARTITIONS 表示数据分区数；否则，它的值是 1。在分区数据库环境中，如果 FPAGES <= (表的数据库分区组中的数据库分区数) * (NPARTITIONS * 1 个扩展数据块大小)，那么建议您不要进行表重组。

在重组表或索引之前，请考虑查询性能不断降低所浪费的成本与重组表或索引所需的成本（其中包括处理时间、耗用时间和并行性降低）哪个更高，以确定是否重组。

2.重组表和索引的成本

执行带有 REBUILD 选项的表重组或索引重组会导致一定量的开销，决定是否重组对象时必须考虑此开销。
带有 REBUILD 选项重组表或重组索引的成本包括：

• 执行实用程序所需的处理时间。
• 运行 REORG 实用程序时，锁定将导致并行性下降。
• 额外的存储需求。
  
  • 脱机表重组需要较多的存储空间来存放表的影子副本。
  • 联机或原位置表重组需要的日志空间较多。
  • 脱机索引重组需要的日志空间较少，并且不涉及影子副本。
  • 联机索引重组需要的日志空间较多，并且需要较多的存储空间来存放索引的影子副本。

在某些情况下，已重组的表可能比原始表要大。在下列情况下，重组后的表可能会增大：

• 在使用索引来确定行顺序的集群重组表操作中，如果表记录的长度可变，那么可能需要更多空间，这是因为在重组后的表中，某些页中包含的行数可能比原始表中的行数少。
• 在上次重组之后，已增加在每一页上留下的可用空间量（由 PCTFREE 值表示）。

脱机表重组的空间需求

由于脱机重组使用影子副本方法（uses a shadow copy approach），因此需要足够的额外的存储空间 来 容纳表的另一个副本。将在原始表所在的表空间或用户指定的临时表空间中构建影子副本。

如果使用表扫描排序方法，那么可能需要其他临时表空间 来进行排序处理。需要的其他空间可能与要重组的表一样大。如果集群索引具有系统管理的空间 (SMS) 类型或唯一的数据库管理的空间 (DMS) 类型，那么重新创建此索引时，不需要进行排序。但是，将通过扫描刚刚重组的数据来重建此索引。重新创建的任何其他索引都需要进行排序，并可能需要 多达所重组的表大小的临时空间。

脱机表重组操作生成的控制日志记录很少，因此耗用的日志空间量相对较少。如果 REORG 实用程序未使用索引，那么将只创建表数据日志记录。如果指定了索引，或者已对该表定义集群索引，那么将按照记录标识（RID）放入新版本的表的顺序来记录这些 RID。每个 RID 日志记录最多包含 8000 个 RID，每个 RID 耗用 4 个字节。这可能加剧脱机表重组操作期间的日志空间问题。请注意，仅当数据库可恢复时，才会记录 RID。

联机表重组的日志空间需求

联机表重组操作所需的日志空间通常比脱机表重组操作所需的日志空间要大。需要的空间大小由所要重组的行数、索引数、索引键的大小以及最开始时表的组织情况决定。为表的日志空间耗用情况确定一个典型的基准是一种不错的做法。

表中的每一行都有可能在联机表重组操作期间被移动两次¹。对于每个索引，每个表行都必须更新索引键以反映新位置，并且在完成所有对旧位置的访问后，将再次更新索引键以除去对旧 RID 的引用。移回行时，将再次执行对索引键的更新。系统将记录所有这些活动，以使联机表重组操作完全可恢复。假定存在一个索引，对于每一行，最少有两个数据日志记录（每个都包含行数据）和四个索引日志记录（每个都包含键数据）。集群索引尤其容易填满索引页，从而导致索引分割和合并，这些分割和合并活动也必须进行记录。

由于联机表 REORG 实用程序发出频繁的内部 COMMIT 语句，因此通常不会产生大量活动日志。截断阶段的情况例外，在此阶段，此实用程序将请求获取 S 表锁定。如果此实用程序无法获取该锁定，那么它将等待，其他事务在此期间可能会迅速填满日志。

3.减少重组表和索引的需要-合理设计以减少碎片生成

您可以使用不同的策略来减少重组表和索引的需要以及相关联的成本。

减少重组表的需要

要减少重组表的需要：

• 使用多分区表。
• 创建多维集群 (MDC) 表。MDC表 在你用 CREATE TABLE 语句的ORGANIZE BY DIMENSIONS子句中指定的多个列 上来维护集群、但是，如果reorgchk实用程序探测到有很多未使用的块（blocks）或blocks应该被压缩时，它仍然会建议你重组MDC表，
• 创建插入时间集群 (ITC) 表。对于ITC表，如果你有一个循环的访问模式，例如you delete all data that was inserted at similar times（你删除了在相似时间点插入的所有数据，or，几乎在插入数据的同时删除所有数据），你可以向系统释放空间。在这种情况下，你可以 使用 释放空间的REORG RECLAIM EXTENTS 命令 来减少表重组的需要。
• 对表启用 APPEND 方式。例如，如果新行的索引键值总是新的大键值，那么表的集群属性会尝试将其放在表的末尾。在这种情况下，启用 APPEND 方式可能优于使用集群索引。

为了进一步减少重组表的需要，请在创建表后执行下列任务：

• 对该表进行更改，以指定装入或表重组操作期间要在每一页中保留的可用空间所占的百分比（PCTFREE）
• 创建集群索引并指定 PCTFREE 选项
• 将数据装入到表中之前对数据进行排序

执行这些任务后，表的集群索引和 PCTFREE 设置有助于保持原来的排序顺序。如果表页中有足够的空间，那么可以将新数据插入正确的页，以保持索引的集群特征。但是，随着越来越多的数据被插入，表页会因此而变满，记录会被追加至表的末尾，因而表将逐渐失去集群特性。

如果在创建集群索引后执行表重组操作或者执行排序和装入操作，那么索引将尝试维护数据的顺序，这将改善 RUNSTATS 实用程序所搜集的 CLUSTERRATIO 或 CLUSTERFACTOR 统计信息。

注：
如果已启用提前读预取，那么它有助于减少重组表的需求，即使 REORGCHK 命令的公式 F4 另行声明也是如此。

减少重建索引的需要

要通过索引重组减少对重建索引的需要，请执行以下操作：

• 创建索引并指定 PCTFREE 或 LEVEL2 PCTFREE 选项。
• 创建索引并指定 MINPCTUSED 选项。或者，考虑使用 REORG INDEXES 命令的 CLEANUP ALL 选项来合并叶子页。
• 使用 REORG INDEXES 命令的 RECLAIM EXTENTS 选项以联机方式将空间释放回表空间。此操作提供空间回收而不需要完全重建索引。

注：
如果已启用提前读预取，那么它有助于使用索引重组减少重建索引的需求，即使 REORGCHK 命令的公式 F4 另行声明也是如此。

表和索引的自动维护

暂不建议