REORGCHK command

One of the following authorities:

• SYSADM or DBADM authority
• CONTROL privilege on the table.

Unless you specify the CURRENT STATISTICS option, REORGCHK gathers statistics on all columns using the default options only. Specifically, column group are not gathered and if LIKE statistics were previously gathered, they are not gathered by REORGCHK. The statistics gathered depend on the kind of statistics currently stored in the catalog tables:

• 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.

REORGCHK calculates statistics obtained from eight different formulas to determine if performance has deteriorated or can be improved by reorganizing a table or its indexes. When a table uses less than or equal to ( NPARTITIONS * 1 extent size ) of pages, no table reorganization is recommended based on each formula. More specifically,

• 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

REORGCHK uses the following formulas to analyze the physical location of rows and the size of the table:

• 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.

• 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).

  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 to FPAGES = 'number of database partitions in a database partition group of the table' * NPARTITIONS * 1 extent size.

  For MDC or ITC tables, the formula is:

  100 * activeblocks /((fpages_adjust / tExtentSize)-(numberOfTablePartitions * numberOfDatabasePartitions))

REORGCHK uses the following formulas to analyze the indexes and their the relationship to the table data:

• 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. It is more appropriate to rely on the performance of the query when deciding on whether to issue the REORG command.
• 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))

• 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

The amount of space needed for all index entries in an uncompressed index is as follows:

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.

In a compressed index, 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.

The following sample table statistics output is for the partitioned table MYDPARTT1. The table has three data partitions with default partition names PART0, PART1, and PART2.

Table statistics:

SCHEMA.NAME                 CARD     OV     NP     FP ACTBLK    TSIZE  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
                               -      -      -      -      -        -   -   -   - ---
----------------------------------------------------------------------------------------

The following sample index statistics output is a partitioned table MYDPARTT1. The table has three data partitions, one nonpartitioned index MYNONPARTIDX1, and one partitioned index MYDPARTIDX1.

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
                                  -     -     -    -     -       - ...  -   -   -   -   - ----- 
------------------------------------------------------------------ ... ------------------------