REORGCHK command
The REORGCHK command provides table or index statistics that indicate whether reorganization may result in improved usage or disk space, and/or improved performance.
Scope
This command can be issued from any database partition in the db2nodes.cfg file. It can be used to update table and index statistics in the catalogs.
Authorization
- SYSADM or DBADM authority
- CONTROL privilege on the table.
Required connection
Database
Command syntax
Command parameters
- UPDATE STATISTICS
- Calls the RUNSTATS routine to update table and index statistics, and then
uses the updated statistics to determine if table or index reorganization is required.
If a portion of the table resides on the database partition where REORGCHK has been issued, the utility executes on this database partition. If the table does not exist on this database partition, the request is sent to the first database partition in the database partition group that holds a portion of the table. RUNSTATS then executes on this database partition.
- CURRENT STATISTICS
- Uses the current table statistics to determine if table reorganization may be required.
- ON SCHEMA schema-name
- Checks all the tables created under the specified schema.
- ON TABLE
-
- USER
- Checks the tables that are owned by the run time authorization ID.
- SYSTEM
- Checks the system tables.
- ALL
- Checks all user and system tables.
- table-name
- Specifies the table to check. The name or alias in the form: schema.table-name can be used. The schema is the user name under which the table was created. If you omit the schema name, the current schema is assumed. If the table specified is a system catalog table, the schema is SYSIBM. For typed tables, the specified table name must be the name of the hierarchy's root table.
Examples
db2 reorgchk update statistics on table system
In the resulting output, the
terms for the table statistics (formulas 1-3) mean: - CARD
- (CARDINALITY) Number of rows in base table.
- OV
- (OVERFLOW) Number of overflow rows.
- NP
- (NPAGES) Number of pages that contain data.
- FP
- (FPAGES) Total number of pages.
- ACTBLK
- Total number of active blocks for a multidimensional clustering (MDC) or insert time clustering (ITC) table. This field is only applicable to tables defined using the ORGANIZE BY clause. It indicates the number of blocks of the table that contain data.
- TSIZE
- Table size in bytes. Calculated as the product of the number of rows in the table (CARD) and the average row length. For long fields and LOBs only the approximate length of the descriptor is used. The actual long field or LOB data is not counted in TSIZE.
- TABLEPAGESIZE
- Page size of the table space in which the table data resides.
- NPARTITIONS
- Number of partitions if this is a partitioned table, otherwise 1.
- F1
- Results of Formula 1.
- F2
- Results of Formula 2.
- F3
- Results of Formula 3.
- REORG
- Each hyphen (-) displayed in this column indicates that the calculated results were within the
set bounds of the corresponding formula, and each asterisk (*) indicates that the calculated results
exceeded the set bounds of its corresponding formula.
-
or*
on the left side of the column corresponds to F1 (Formula 1)-
or*
in the middle of the column corresponds to F2 (Formula 2)-
or*
on the right side of the column corresponds to F3 (Formula 3).
Table reorganization is suggested when the results of the calculations exceed the bounds set by the formula.
For example,
---
indicates that, since the formula results of F1, F2, and F3 are within the set bounds of the formula, no table reorganization is suggested. The notation*-*
indicates that you can investigate whether the conditions represented by F1 and F3 are affecting your workloads. If the conditions represented by F1 and F3 are affecting your workload, you might consider doing a table reorganization. The notation*--
indicates that F1 is the only formula that is exceeding its bounds.Table reorganization advice for more table availability is as follows:Note: Table reorganization using theCLASSIC
clause can address all of F1, F2, F3- F1 (overflows): For regular tables, too many overflows can lead to poor performance when they
drive sync I/O. If only F1 is suggested, consider
REORG TABLE INPLACE CLEANUP OVERFLOWS
option. This leaves the table fully available as overflows are removed in the background. - F2: Total size of table by data as a percentage of allocated pages. If reclaiming space is
desired, classic reorganization or inplace reorganization without the
NOTRUNCATE
option can return unneeded space to the tablespace, but the table will not be fully available for some portions of time. For MDC and ITC tables,RECLAIM SPACE
clause will reclaim the space, by default online. - F3 NPAGES versus FPAGES: If F3 is not recommending reorganization but F2 is, then the table may
have many pages which are partially full. This can lead to performance issues as it will require
more pages to be read in for a given query. If this is the case, then an inplace reorganization
(without
CLEANUP OVERFLOWS ONLY
) can move rows to reduce NPAGES and improve performance with theNOTRUNCATE
option while leaving the table fully online. If F3 recommends a reorganization and reclaiming space is important, then any method described above for F2 will address this.
The table name is truncated to 30 characters, and the ">" symbol in
the thirty-first column represents the truncated portion of the table name. An *
suffix to a
table name indicates it is an MDC or ITC table. An *
suffix to an index name indicates it is
an MDC or ITC dimension index.
- INDCARD
- (INDEX CARDINALITY) Number of index entries in the index. This could be different than table cardinality for some indexes. For example, for indexes on XML columns the index cardinality is likely greater than the table cardinality.
- LEAF
- Total number of index leaf pages (NLEAF). Depending on whether the index is partitioned, this value comes from the NLEAF column of SYSCAT.INDEXES or SYSCAT.INDEXPARTITIONS.
- ELEAF
- Number of pseudo empty index leaf pages (NUM_EMPTY_LEAFS)
A pseudo empty index leaf page is a page on which all the RIDs are marked as deleted, but have not been physically removed.
- NDEL
- Number of pseudo deleted RIDs (NUMRIDS_DELETED)
A pseudo deleted RID is a RID that is marked deleted. This statistic reports pseudo deleter RIDs on leaf pages that are not pseudo empty. It does not include RIDs marked as deleted on leaf pages where all the RIDs are marked deleted.
- KEYS
- Number of unique index entries that are not marked deleted (FULLKEYCARD)
- LEAF_RECSIZE
- Record size of the index entry on a leaf page. This is the average size of the index entry excluding any overhead and is calculated from the average column length of all columns participating in the index.
- NLEAF_RECSIZE
- Record size of the index entry on a non-leaf page. This is the average size of the index entry excluding any overhead and is calculated from the average column length of all columns participating in the index except any INCLUDE columns.
- LEAF_PAGE_OVERHEAD
- Reserved space on the index leaf page for internal use.
- NLEAF_PAGE_OVERHEAD
- Reserved space on the index non-leaf page for internal use.
- INDEXPAGESIZE
- Page size of the table space in which the index resides, specified at the time of index or table creation. If not specified, INDEXPAGESIZE has the same value as TABLEPAGESIZE.
- LVLS
- Number of index levels (NLEVELS)
- PCTFREE
- Specifies the percentage of each index page to leave as free space, a value that is assigned when defining the index. Values can range from 0 to 99. The default value is 10.
- LEAF_RECSIZE_OVERHEAD
- Index record overhead on a leaf page. For indexes on tables in LARGE table spaces the overhead is 11 for partitioned tables and 9 for other tables. For indexes on tables in REGULAR table spaces these values are 9 for partitioned tables and 7 for others. The only exception to these rules are XML paths and XML regions indexes where the overhead is always 9. This information is also available in the following table for easy reference.
- NLEAF_RECSIZE_OVERHEAD
- Index record overhead on a non-leaf page. For indexes on tables in LARGE table spaces the overhead is 14 for partitioned tables and 12 for other tables. For indexes on tables in REGULAR table spaces these values are 12 for partitioned tables and 10 for others. The only exception to these rules are XML paths and XML regions indexes where the overhead is always 12. This information is also available in the following table for easy reference.
- DUPKEYSIZE
- Size of duplicate keys on index leaf pages. For indexes on tables in LARGE table spaces the
DUPKEYSIZE is 9 for partitioned tables and 7 for other tables. For indexes on tables in REGULAR
table spaces these values are 7 for partitioned tables and 5 for others. The only exception to these
rules are XML paths and XML regions indexes where the DUPKEYSIZE is always 7. This information is
also available in the following table for easy reference.
Table 1. LEAF_RECSIZE_OVERHEAD, NLEAF_RECSIZE_OVERHEAD, and DUPKEYSIZE values are a function of index type, table partitioning, and table space type (REGULAR table space) Variable Regular Table (XML paths or regions index) Regular Table (All other indexes) Partitioned Table (All indexes) LEAF_RECSIZE_OVERHEAD 9 7 9 NLEAF_RECSIZE_OVERHEAD 12 10 12 DUPKEYSIZE 7 5 7 Table 2. LEAF_RECSIZE_OVERHEAD, NLEAF_RECSIZE_OVERHEAD, and DUPKEYSIZE values are a function of index type, table partitioning, and table space type (LARGE table space**) Variable Regular Table (XML paths or regions index) Regular Table (All other indexes) Partitioned Table (All indexes) LEAF_RECSIZE_OVERHEAD 9 9 11 NLEAF_RECSIZE_OVERHEAD 12 12 14 DUPKEYSIZE 7 7 9 ** For indexes on tables in large table spaces the indexes will be assumed to have large RIDs. This may cause some of the formulas to give inaccurate results if the table space of the table was converted to large but the indexes have not yet been recreated or reorganized.
- F4
- Results of Formula 4.
- F5
- Results of Formula 5.
- F6
- Results of Formula 6.
- F7
- Results of Formula 7.
- F8
- Results of Formula 8.
- REORG
- Each hyphen (-) displayed in this column indicates that the calculated results were within the
set bounds of the corresponding formula, and each asterisk (*) indicates that the calculated result
exceeded the set bounds of its corresponding formula.
-
or*
on the left column corresponds to F4 (Formula 4)-
or*
in the second from left column corresponds to F5 (Formula 5)-
or*
in the middle column corresponds to F6 (Formula 6).-
or*
in the second column from the right corresponds to F7 (Formula 7)-
or*
on the right column corresponds to F8 (Formula 8).
Index reorganization advice is as follows:- If the results of the calculations for Formula 1, 2 and 3 do not exceed the bounds set by the formula and the results of the calculations for Formula 4 do exceed the bounds set, then index reorganization is recommended.
- If the results of the calculations for Formula 5 and/or 6 exceed the bounds set by the formula then reclaiming the extents of the indexes using the RECLAIM EXTENTS option of index reorganization is recommended. If the RECLAIM EXTENTS option does not bring Formula 5 and/or 6 into the bounds set by the formula, then index reorganization with the REBUILD option is recommended.
- If only the results of the calculations Formula 7 exceed the bounds set, but the results of Formula 1, 2, 3, 4, 5 and 6 are within the set bounds, then cleanup of the indexes using the CLEANUP option of index reorganization is recommended.
- If the only calculation result to exceed the set bounds is the that of Formula 8, then a cleanup of the pseudo empty pages of the indexes using the CLEANUP PAGES option of index reorganization is recommended.
- The RECLAIMABLE_SPACE column in the admin_get_tab_info and admin_get_index_info functions show the amount of reclaimable space, in kilobytes, for tables and indexes. You can use this value to determine when to run a reorganization with the RECLAIM EXTENTS option. For more details on RECLAIMABLE_SPACE, and how to evaluate the effectiveness of space reclaim, see the related links.
On a partitioned table the results for formulas (5 to 8) can be misleading depending on when the statistics are collected. When data partitions are detached, the index keys for the detached partition are not cleaned up immediately. Instead, the cleanup is deferred and eventually the keys are removed by index cleaners which operate asynchronously in the background (this is known as Asynchronous Index Cleanup or AIC). While the index keys pending cleanup exist in the index, they will not be counted as part of the keys in the statistics because they are invisible and no longer part of the table. As a result, statistics collected before asynchronous index cleanup is run will be misleading. If the REORGCHK command is issued before asynchronous index cleanup completes, it will likely generate a false alarm for index reorganization or index cleanup based on the inaccurate statistics. Once asynchronous index cleanup is run, all the index keys that still belong to detached data partitions which require cleanup will be removed and this may eliminate the need for index reorganization.
For partitioned tables, you are encouraged to issue the REORGCHK after an asynchronous index cleanup has completed in order to generate accurate index statistics in the presence of detached data partitions. To determine whether or not there are detached data partitions in the table, you can check the status field in the SYSCAT.DATAPARTITIONS catalog view and look for the value I (index cleanup), L (logically detached), or D (detached with dependent MQT).
Usage notes
This command does not display statistical information for declared temporary tables or created temporary tables.
This utility does not support the use of nicknames.
A new server release might introduce new table and index features. These new features might impact REORGCHK logic, that is, how REORGCHK computes REORG recommendations. If REORGCHK is issued from a client not at the same level as the server, it might report different results than those reported from a client at the same level as the server. REORGCHK is a client application, therefore, REORGCHK should be run from a client running the same level as the server. Doing so ensures the most accurate report is generated. For server administrative work, in general, use a client and a server at the same level.
- If detailed index statistics are present in the catalog for any index, table statistics and detailed index statistics (without sampling) for all indexes are collected.
- If detailed index statistics are not detected, table statistics as well as regular index statistics are collected for every index.
- If distribution statistics are detected, distribution statistics are gathered on the table. If distribution statistics are gathered, the number of frequent values and quantiles are based on the database configuration parameter settings.
- For non-partitioned tables ( NPARTITIONS =1 ), the threshold is:
(FPAGES <= 1 extent size)
- For partitioned tables, it is:
(FPAGES <= NPARTITIONS * 1 extent size)
- In a multi-partitioned database, after the number of database partitions in a database partition
group of the table is considered, this threshold for not recommending table reorganization changes
to:
FPAGES <= 'number of database partitions in a database partition group of the table' * NPARTITIONS * 1 extent size
Long field or LOB data is not accounted for while calculating TSIZE.
- Formula F1:
100*OVERFLOW/CARD < 5
The total number of overflow rows in the table should be less than 5 percent of the total number of rows. Overflow rows can be created when rows are updated and the new rows contain more bytes than the old ones (VARCHAR fields), or when columns are added to existing tables. However, even with fixed length columns, overflows may be introduced when compression is on the table.
- Formula F2: For regular tables:
100*TSIZE / ((100-TPCTFREE)/100 * (FPAGES-NPARTITIONS) * (TABLEPAGESIZE-68)) > 70
The table size in bytes (TSIZE) should be more than 70 percent of the total space allocated for the table. (There should be less than 30% free space.) The total space allocated for the table depends upon the page size of the table space in which the table resides (minus an overhead of 68 bytes). Because the last page allocated in the data object is not usually filled, 1 is subtracted from FPAGES for each partition (which is the same as FPAGES-NPARTITIONS). When table compression is used, FPAGES is adjusted to account for the estimated dictionary size.
Regular tables in REGULAR table spaces are limited to 255 rows on a data page. If you are using a large page size and short table rows, data page space might be wasted depending on how rows are stored on the page. In this case, the formula F2 might get a value that is less than 70. However, reorganizing the table might not recover the data page space because of the 255 row limit. If you want to use the wasted space, you should either use a smaller page size or convert your REGULAR table spaces to LARGE table spaces, and then reorganize the table. This does not affect tables that reside in LARGE table spaces.
For MDC tables:100*TSIZE / ((ACTBLK-FULLKEYCARD) * EXTENTSIZE * (TABLEPAGESIZE-68)) > 70
FULLKEYCARD represents the cardinality of the composite dimension index for the MDC table. Extentsize is the number of pages per block. The formula checks if the table size in bytes is more than the 70 percent of the remaining blocks for a table after subtracting the minimum required number of blocks.
- Formula F3:
100*NPAGES/FPAGES > 80
The number of pages that contain no rows at all should be less than 20 percent of the total number of pages. (Pages can become empty after rows are deleted.) As previously noted, no table reorganization is recommended when (
FPAGES <= NPARTITIONS * 1 extent size
). Therefore, F3 is not calculated. For non-partitioned tables,NPARTITIONS = 1
. In a multi-partitioned database, this condition changes toFPAGES = 'number of database partitions in a database partition group of the table' * NPARTITIONS * 1 extent size
.For MDC or ITC tables this formula indicates the percentage of used blocks instead of pages, the formula is:100 * activeblocks / ( (fpages_adjust / tExtentSize) - (numberOfTablePartitions * numberOfDatabasePartitions) )
- Formula F4:
- For non-partitioned tables:
CLUSTERRATIO or normalized CLUSTERFACTOR > 80
The global CLUSTERFACTOR and CLUSTERRATIO take into account the correlation between the index key and distribution key. The clustering ratio of an index should be greater than 80 percent. When multiple indexes are defined on one table, some of these indexes have a low cluster ratio. (The index sequence is not the same as the table sequence.) This cannot be avoided. Be sure to specify the most important index when reorganizing the table. The cluster ratio is usually not optimal for indexes that contain many duplicate keys and many entries.
- For partitioned tables:
AVGPARTITION_CLUSTERRATIO or normalized AVGPARTITION _CLUSTERFACTOR > 80
AVGPARTITION_CLUSTERFACTOR and AVGPARITITON_CLUSTERRATIO values reflect how clustered data is within a data partition with respect to an index key. A partitioned table can be perfectly clustered for a particular index key within each data partition, and still have a low value for the CLUSTERFACTOR and CLUSTERRATIO because the index key is not a prefix of the table partitioning key. Design your tables and indexes using the most important index keys as a prefix of the table partitioning key. In addition, because the optimizer uses the global clusteredness values to make decisions about queries that span multiple data partitions, it is possible to perform a clustering reorganization and have the optimizer still not choose the clustering index when the keys do not agree.
Note:- If readahead prefetching is enabled, formula F4 might not be a good indicator for issuing the REORG command. When you decide whether to issue the REORG command, it is more appropriate to rely on the performance of the query.
- For a random ordering of index key columns, the CLUSTERRATIO is low. This number is low since a random ordering of index key columns makes the index keys out of order versus the insert order of the data. A random ordering cannot be used as a clustering index. For these reasons, the result of formula F4 for REORGCHK always indicates that a reorganization is not required if the index has a random ordering. The same is true when you use the REORGCHK_IX_STATS stored procedure.
- For non-partitioned tables:
- Formula F5:
- For a single database partition:
100*(KEYS*(LEAF_RECSIZE+LEAF_RECSIZE_OVERHEAD)+(INDCARD-KEYS)*DUPKEYSIZE) / ((LEAF-NUM_EMPTY_LEAFS-1)* (INDEXPAGESIZE-LEAF_PAGE_OVERHEAD)) > MIN(50,(100-PCTFREE))
The percentage of allocated space in use at the leaf level of the index should be greater than the minimum of 50 and 100-PCTFREE percent, where PCTFREE is defined by the CREATE INDEX statement. If the space used is less than this value, then the index could be made smaller by running REORG. This formula is only checked when LEAF>1 since you cannot have an index with less than 1 leaf page.
- For a multi-partition database
environment:
100*(KEYS*(LEAF_RECSIZE+LEAF_RECSIZE_OVERHEAD)+(INDCARD-KEYS)*DUPKEYSIZE) / ((LEAF-NUM_EMPTY_LEAFS-NPARTITIONS)*(INDEXPAGESIZE-LEAF_PAGE_OVERHEAD)) > MIN(50,(100-PCTFREE))
- For a single database partition:
- Formula F6:
(( 100-PCTFREE ) * ((FLOOR((100 - LEVEL2PCTFREE) / 100 * (INDEXPAGESIZE-NLEAF_PAGE_OVERHEAD)/(NLEAF_RECSIZE+NLEAF_RECSIZE_OVERHEAD)))* (FLOOR((100-MIN(10, LEVEL2PCTFREE))/100*(INDEXPAGESIZE-NLEAF_PAGE_OVERHEAD)/ (NLEAF_RECSIZE+NLEAF_RECSIZE_OVERHEAD)) ** (NLEVELS-3)) * (INDEXPAGESIZE-LEAF_PAGE_OVERHEAD))/(KEYS*(LEAF_RECSIZE+LEAF_RECSIZE_OVERHEAD)+ (INDCARD-KEYS) * DUPKEYSIZE)) < 100
To determine if recreating the index would result in a tree having fewer levels. This formula checks the ratio between the amount of space in an index tree that has one less level than the current tree, and the amount of space needed. If a tree with one less level could be created and still leave PCTFREE available, then a reorganization is recommended. The actual number of index entries should be more than (100-PCTFREE) percent of the number of entries an NLEVELS-1 index tree can handle (only checked if NLEVELS>2). In the case where NLEVELS = 2, the other REORGCHK formulas should be relied upon to determine if the index should be reorganized.
In simplified form, formula F6 can be rewritten in the following form:Amount of space needed for an index if it was one level smaller --------------------------------------------------------------- < 1 Amount of space needed for all the entries in the index
When the above left part is > 1, it means all index entries in the existing index can fit into an index that is one level smaller than the existing index. In this case, a reorg index is recommended.
The amount of space needed for an NLEVELS-1 index is calculated by:(The max number of leaf pages that a NLEVELS-1 index can have) * (Amount of space available to store index entries per leaf page)
where,
The max number of leaf pages that a NLEVELS-1 index can have = (No. of entries a level 2 index page can have) * (No. of entries per page on levels greater than 2) ** (No. of levels in the intended index - 2) = (100 - LEVEL2PCTFREE) { FLOOR( [----------------------------] * 100 (PageSize - Overhead) [-------------------------------------------] ) * (Avg. size of each nonleaf index entry) (100 - MIN(10, LEVEL2PCTFREE)) FLOOR([------------------------------------] * 100 (PageSize - Overhead) [----------------------------------------------------])** (Avg. size of each nonleaf index entry) (NLEVELS-3) } (100 - LEVEL2PCTFREE) is the percentage of used space on level 2 of the index. Level 2 is the level immediately above the leaf level. (100 - MIN(10, LEVEL2PCTFREE)) is the percentage of used space on all levels above the second level. NLEVELS is the number of index levels in the existing index. The amount of space available to store index entries per leaf page = ((100-PCTFREE)/100 * (INDEXPAGESIZE - LEAF_PAGE_OVERHEAD)) = ( Used space per page * (PageSize - Overhead) ) The amount of space needed for all index entries: KEYS * (LEAF_RECSIZE + LEAF_RECSIZE_OVERHEAD) + (INDCARD - KEYS) * DUPKEYSIZE
(KEYS * (LEAF_RECSIZE + LEAF_RECSIZE_OVERHEAD)) represents the space used for the first occurrence of each key value in the index and ((INDCARD - KEYS) * DUPKEYSIZE) represents the space used for subsequent (duplicate) occurrences of a key value.
- Formula F7:
100 * (NUMRIDS_DELETED / (NUMRIDS_DELETED + INDCARD)) < 20
The number of pseudo-deleted RIDs on non-pseudo-empty pages should be less than 20 percent.
- Formula F8:
100 * (NUM_EMPTY_LEAFS/LEAF) < 20
The number of pseudo-empty leaf pages should be less than 20 percent of the total number of leaf pages.
Running statistics on many tables can take time, especially if the tables are large.
Usage notes for index compression
Formula F5 determines the ratio between the amount of space needed by the keys and the amount of space allocated. Formula F6 determines if recreating the index would result in a tree having fewer levels. The following formula checks the ratio between the amount of space in an index tree that has one less level than the current tree, and the amount of space needed. This formula relies on the amount of space needed for all index entries. Both formulae use the amount of space needed for all index entries.
KEYS * (LEAF_RECSIZE + LEAF_RECSIZE_OVERHEAD) +
(INDCARD - KEYS) * DUPKEYSIZE
where LEAF_RECSIZE
is the average size of index key and DUPKEYSIZE
is the size of a RID. LEAF_RECSIZE
is affected by prefix compression.
DUPKEYSIZE
is not a reliable way to measure the size of duplicate keys on indexes.
The amount of space needed in a compressed index is the amount of space needed for all uncompressed
index entries multiplied by the index compression ratio.
(KEYS * (LEAF_RECSIZE + LEAF_RECSIZE_OVERHEAD) +
(INDCARD - KEYS) * DUPKEYSIZE) * COMPRESSION_RATIO
where COMPRESSION_RATIO
is the estimated index compression ratio in the index. The
COMPRESSION_RATIO is calculated as: (100 - PCT_PAGES_SAVED) / 100
where
PCT_PAGES_SAVED
is the estimated percentage of leaf pages saved from index
compression. This value is taken from the catalogs. If statistics are not collected,
PCT_PAGES_SAVED
is -1 in the catalogs, and COMPRESSION_RATIO
is 1. Both the REORGCHK command and the REORGCHK_IX_STATS procedure show the
PCT_PAGES_SAVED
value.
Usage notes for partitioned tables
For a data partitioned table, REORGCHK returns statistics and reorganization recommendations for the table and the data partitions of the table.
For table statistics and reorganization recommendations, REORGCHK lists the table information that contains the SCHEMA.NAME for the table, the table level statistics, and reorg recommendation. After the table information, the information for each data partition information is listed. For each partition, the information includes the SCHEMA.NAME for the table, the partition name, the table statistics for the partition, and reorganization recommendation for the partition.
For index statistics and reorganization recommendations, REORGCHK returns the SCHEMA.NAME for each table followed by the fully qualified index name and index statistics and index reorganization recommendation for each nonpartitioned index on the table. If the table has partitioned indexes, REORGCHK returns the information for partitioned indexes after the nonpartitioned indexes. REORGCHK returns the following information for each data partition of the table, the fully qualified index name, the partition name, index statistics for the partition, and index reorganization recommendations for the partition.
To provide better data availability to a data partitioned table, a reorganization of a specific data partition can be performed if recommended. REORG TABLE with the ON DATA PARTITION clause supports reorganizing a partition of a table.
For partitioned indexes, index reorganization of all indexes for a specific data partition can be performed using REORG INDEXES ALL with the ON DATA PARTITION clause if recommended.
The REORG INDEX command can be used to reorganized a nonpartitioned index on a data partitioned table.
See the REORG column for information about reorganization recommendations for data partitioned tables.
Table statistics:
SCHEMA.NAME CARD OV NP FP ACTBLK SIZE F1 F2 F3 REORG
-----------------------------------------------------------------------
Table: USER1.MYDPARTT1
- - - - - - - - - ---
Table: USER1.MYDPARTT1
Data Partition: PART0
- - - - - - - - - ---
Table: USER1.MYDPARTT1
Data Partition: PART1
- - - - - - - - - ---
Table: USER1.MYDPARTT1
Data Partition: PART2
- - - - - - - - - ---
-----------------------------------------------------------------------
Index statistics:
SCHEMA.NAME INDCARD LEAF ELEAF LVLS NDEL KEYS ... F4 F5 F6 F7 F8 REORG
------------------------------------------------------------ ... --------------------
Table: USER1.MYDPARTT1
Index: USER1.MYNONPARTIDX1
- - - - - - ... - - - - - -----
Index: USER1.MYDPARTIDX1
Data Partition: PART0
- - - - - - ... - - - - - -----
Index: USER1.MYDPARTIDX1
Data Partition: PART1
- - - - - - ... - - - - - -----
Index: USER1.MYDPARTIDX1
Data Partition: PART2
- - - - - - ... - - - - - -----
------------------------------------------------------------ ... --------------------