IBM DB2 中的 reorg 命令有什么用？
ibm db2 db 中的 reorg 命令有什么用？ Reorg 内部做什么？ 如果在表上创建了新索引，是否需要运行 reorg？
What is the use of reorg command in ibm db2 db ? What does Reorg do internally ?
Is it necessary to run reorg if new indexes are created on the table ?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（3）
REORG 操作执行以下功能：
检查引用完整性（如果适用于目标表），并删除违反引用完整性的行或使任何受影响的索引无效。 （引用完整性是表中的每个外键值作为引用表中的主键值存在的关系属性。
对一个或多个索引执行内部重组。表（所有类型）以提高该信息的内部存储，从而提高使用索引访问数据时的性能。它可以重建所有索引，选择性地重建一个或多个命名索引，或者选择性地重建一个或多个段。更多命名索引。
填充使用 CREATE INDEX 语句创建的 DEFERRED 索引。 DEFERRED 索引是一个空索引结构，可以在以后填充。
重建预先计算的视图。例如，如果应用程序中的预计算视图维护设置为 OFF，则可以使用 REORG 命令仅重建预计算视图，而无需触及目标表上的索引。或者，您可以使用 REORG 命令来重建索引和视图。
除了重建聚合表数据外，REORG 命令还会重建聚合表上的索引。
在以下情况下需要执行 REORG 操作：
如果使用数据库还原操作来还原表或索引的各个段，则需要重建受影响的索引。
每当对数据库的修改影响超过 30% 的数据时，请对任何直接修改的表运行带有 REORG 语句的 TMU。使用 REORG 语句定期重建此类表和索引可确保引用完整性和最佳性能。
重组无效的 STAR 索引。某些操作可能会使 STAR 索引失效。例如，增加表上的 MAXROWS PER SEGMENT 或 MAXSEGMENTS 参数，或者使用 ALTER 语句扩展段，可能会使引用已更改表的表上的 STAR 索引无效。这些操作总是会生成一条警告消息，指出基于更改的表的 STAR 索引可能无效，在这种情况下需要重新组织受影响的 STAR 索引。您可以在发出消息时重新组织受影响的索引，也可以安排在更方便的时间进行 REORG 操作。但是，针对具有无效索引的表的任何非查询（INSERT、UPDATE 或 DELETE）操作都会导致一条错误消息，指示必须重新组织索引。您必须先执行 REORG 操作，然后才能访问表以进行 INSERT、UPDATE、DELETE 或 LOAD 操作。
在以下情况下不需要 REORG：
如果除了完全加载数据之外没有对数据库进行任何更改。
如果表和索引被类似地分段，并且新索引数据被加载到与新表段相对应的新索引段中。
The REORG operation performs the following functions:
Checks referential integrity, if applicable for the target table, and either deletes rows that violate it or invalidates any affected indexes. (Referential integrity is the relational property that each foreign-key value in a table exists as a primary-key value in the referenced table.
Performs an internal reorganization of one or more of the indexes for the table (all types) to improve the internal storage of this information and thereby the performance when the index is used to access data. It can rebuild all indexes, selectively rebuild one or more named indexes, or selectively rebuild one or more segments of one or more named indexes.
Populates a DEFERRED index that is created with a CREATE INDEX statement. A DEFERRED index is an empty index structure that can be populated at a later date.
Rebuilds precomputed views. For example, if precomputed view maintenance is set to OFF in your application, you can use the REORG command to rebuild precomputed views only, without touching the indexes on the target table. Alternatively, you can use the REORG command to rebuild both indexes and views.
In addition to rebuilding aggregate table data, the REORG command rebuilds indexes on aggregate tables.
A REORG operation is necessary in the following cases:
To rebuild the affected indexes if you use a database restore operation to restore individual segments of a table or index.
Whenever modifications to a database affect more than about 30 percent of the data, run the TMU with a REORG statement for any tables directly modified. Periodically rebuilding such tables and indexes with a REORG statement ensures referential integrity and optimal performance.
To reorganize invalid STAR indexes. Certain operations can invalidate STAR indexes. For example, increasing the MAXROWS PER SEGMENT or the MAXSEGMENTS parameter on a table, or using an ALTER statement to expand a segment, can invalidate STAR indexes on tables that reference the altered table. These operations always generate a warning message that says STAR indexes based on the altered table might be invalid, in which case the affected STAR indexes need to be reorganized. You can either reorganize affected indexes when the message is issued or schedule the REORG operation for a more convenient time. However, any non-query (INSERT, UPDATE, or DELETE) operation against a table that has an invalid index results in an error message that says the index must be reorganized. You must perform a REORG operation before the table can be accessed for an INSERT, UPDATE, DELETE, or LOAD operation.
REORG is unnecessary in the following cases:
If no changes are made to the database except by complete loads of data.
If the table and indexes are segmented alike and new index data is loaded into new index segments corresponding to new table segments.
当您在（至少对于 iSeries）DB2 中删除一条记录（或行）时，它会将该记录标记为删除，但不会物理删除该记录。运行 reorg 时，它会获取标记为删除的记录，然后物理删除它们。我相信它还会移动所有数据记录，以便在此过程中实现最佳性能。在 iSeries 上，可以通过告诉文件（或表）重用删除记录来消除这种需求。
正如我所暗示的，我知道 DB2 for iSeries（或 IBM i）就是这种情况。我只能推测这个过程在 DB2 上是类似的。
When you delete a record (or row) in (at least for the iSeries) DB2, it marks the record for deletion but does not physically delete the record. When reorg is run, it takes those records marked for deletion and then physically deletes them. I believe it also moves all of the data records around for optimal performance in this process as well. On the iSeries, this need can be rendered obsolete by telling the file (or table) to reuse delete records.
As I hinted at, I know this is the case on DB2 for iSeries (or IBM i). I can only presume that this process is similar on DB2.
它实际上所做的是将物理记录放回主键顺序，并在正确的位置提供正确的可用空间量（由 PCTFREE 指定）。
显然，这也需要重建索引，从而产生良好平衡的 btree。
What it actually does is put the physical records back into primary key order, with the correct amount of free space (specified by PCTFREE) in the right places.
Obviously this also requires the indexes are rebuilt as well which results in nicely balanced btrees.