15.3.4 Cluster key and clustering data page storage condition analysis

Organization of this subsection
(1) Purpose
(2) Analysis results
(3) Analyzing the analysis results

(1) Purpose

This analysis enables you to determine the degree of disorganization in the storage conditions for cluster keys and clustering data pages, so that you can decide whether or not the database needs to be reorganized.

(2) Analysis results

The following shows the results of cluster key and clustering data page storage condition analysis:

pddbst VV-RR(Object Option) ** Index Status Analysis **     2001/08/22 16:44:01 [1]
Index Name : (CLUSTER0000131198​) [2]
Auth Id    : MANUAL [3]
Table Name : TBL06 [4]


***** Cluster Key Analysis *****
 RD Area Count :          1/         1 [5]
--------------------------------------------------------------------------------
RD Area Name : USER_RDAREA_4                    Server : BES01 [6]
 Original RD Area Name : USER_RDAREA_4 [24]
 Generation Number :  0[25] Replica RD Area Count :  0[26]
 Segment Size:          5 Pages   Page Size :       4096 [7]
 Index Level :          1 [8]
                      Total            Repetitional
 Key Count              100 [9]                   0 [10]
 Row Count              100 [11]                  0 [12]
              Total   Disordered
 Segment          0 [13]       0(  0.0%) [14]
 Page             0 [15]       0(  0.0%) [16]

***** Clustering Table Analysis *****
 RD Area Count :          1/         1 [17]
--------------------------------------------------------------------------------
RD Area Name : USER_RDAREA_4                    Server : BES01 [18]
 Original RD Area Name : USER_RDAREA_4
 Generation Number :  0   Replica RD Area Count :  0
 Segment Size:          5 Pages   Page Size :       4096 [19]
              Total   Disordered
 Segment          0 [20]       0(  0.0%) [21]
 Page             0 [22]       0(  0.0%) [23]

Explanation
  1. Date and time of completion of acquisition of the information required for this condition analysis, in the format YYYY/MM/DD hh:mm:ss.
    YYYY: Year. MM: Month. DD: Date. hh: Hour. mm: Minute. ss: Second.
  2. Index name of the cluster key subject to analysis.
  3. Authorization identifier of the index owner of the cluster key subject to analysis.
  4. Name of the table that defined the cluster key subject to analysis.
  5. Number of RDAREAs that store the index in 2 above, and the number of those RDAREAs whose information could be analyzed normally:
    Number of RDAREAs whose information could be analyzed normally / number of RDAREAs that store the index
  6. Name of an RDAREA that stores the analyzed index, and the name of the server managing that RDAREA.
  7. Segment size (number of pages per segment) and page length (in bytes) of the RDAREA in 6.
  8. Number of index levels of the index key of the RDAREA in 6.
  9. Number of stored keys in the index of the RDAREA in 6 (displayed as an integer of up to 15 digits; a value in excess of 15 digits is displayed exponentially).
  10. Number of keys stored in a duplicate key structure1 in the index of the RDAREA in 6 (same display format as in 9).
    1 Refers to the structure in which management information for the relevant keys in the index page is stored in multiple pages when the number of duplicate key values exceeds 200.
  11. Number of rows stored in the index of the RDAREA in 6 (same display format as in 9).
  12. Number of rows corresponding to 10 (same display format as in 9).
  13. Number of times the storage position of segments in the index of the RDAREA in 6 changed.
  14. In relation to 13, number of times the storage order was incorrect (reverse direction). Parentheses indicate the ratio of segments in incorrect storage order (14/13 [Figure] 100%).
  15. Number of times the storage position of pages in the index of the RDAREA in 6 changed.
  16. In relation to 15, number of times the storage order was incorrect (reverse direction). Parentheses indicate the ratio of segments in incorrect storage order (16/15 [Figure] 100%).
  17. Number of RDAREAs stored in the table in 3, and the number of those RDAREAs whose information could be analyzed normally:
    Number of RDAREAs that could be analyzed normally / number of RDAREAs that store the table
  18. Name of an RDAREA that stores the analyzed table, and the name of the server that manages that RDAREA.
  19. Segment size (number of pages per segment) and page length (in bytes) of the RDAREA in 18.
  20. Number of times the storage position of segments in the index of the RDAREA in 18 changed.
  21. In relation to 20, number of times the storage order was incorrect (reverse direction). Parentheses indicate the ratio of segments in incorrect storage order (21/20 [Figure] 100%).
  22. Number of times the storage position of table pages stored in the RDAREA in 18 changed.
  23. In relation to 22, number of times the storage order was incorrect (reverse direction). Parentheses indicate the ratio of segments in incorrect storage order (23/22 [Figure] 100%).
  24. Name of the original RDAREA.
  25. Generation number of the corresponding RDAREA.
  26. Number of replica RDAREAs.
Note 1
If the cluster key is partitioned and stored in multiple RDAREAs, Nos. 6-17 are displayed for each such RDAREA. The information is displayed in ascending order of the RDAREA names.
Note 2
If the table is partitioned and stored in multiple RDAREAs, Nos. 18-23 are displayed for each such RDAREA. The information is displayed in ascending order of the RDAREA names.
Note 3
Nos. 24-26 are displayed when HiRDB Staticizer Option is used. If a generation is specified for analysis and the specified replica RDAREA generation does not exist, the utility displays * as the RDAREA name and displays only the original RDAREA name, generation number, and server name.
If there is no replica RDAREA for either a table storage RDAREA or a corresponding cluster key index RDAREA, the utility displays only the RDAREA name, original RDAREA name, generation number, and server name for the existing replica RDAREA. For the nonexistent replica RDAREA, the utility displays * as the RDAREA name and displays only the original RDAREA name, generation number, and server name, and omits all subsequent information. If the specified generation of a replica RDAREA for a cluster key index storage RDAREA does not exist, the utility issues a warning message without displaying analysis results.

(3) Analyzing the analysis results

Table 15-4 describes the results of cluster key and clustering data page condition analysis and explains the actions to be taken.

Note that row deletion has no effect on the degree of irregularity in the clustering data conditions in the analysis results. If deletion is the principal table operation performed, you must take the following steps to determine whether or not the database needs to be reorganized:

  1. Determine the number of used free pages (by table condition analysis)
  2. Determine the free area in terms of the numbers of storage rows and used pages

For 2, the following shows the size of the invalid free area:

(Current number of used pages in table1 - number of table storage pages2) [Figure]page size (bytes)

1 Obtain the value from the results of condition analysis by table.

2 To obtain the value, see the HiRDB Version 8 Installation and Design Guide.

Table 15-4 Results of cluster key and clustering data page condition analysis and actions to be taken

TargetAnalysis resultDescription and action
Cluster keysThere are too many rows for the number of storage keys in the index.The key duplication ratio is high, affecting the performance of search operations that use the index.
Check and, if necessary, revise the column structure in the index definition.
There are keys that are stored in a duplicate key structure.1Some keys have (or had) a high degree of duplication, affecting the performance of search operations that use the index.
Check and, if necessary, revise the column structure in the index definition (if this condition existed in the past, you can eliminate the duplicate key structure by using pdrorg to reorganize the index).2
Degree of irregularity is high in the storage order.There is irregularity in the storage order, which is affecting search performance.
Use pdrorg to reorganize the index. If there is irregularity also in the storage order of data pages, reorganizing the table will automatically reorganize the index.
Clustering data pagesDegree of irregularity is high in the storage order.There is irregularity in the storage order of data pages, which is affecting the performance of access operations in the order of cluster key values.
Use pdrorg to reorganize the table.
The following condition is true:
Storage location changes count > storage location changes count or number of used segments in the results of condition analysis by table > number of pages - 1
1 This is a structure for storing on multiple pages the management information for the applicable key on the index page when there are 201 or more duplicate key values. Once a duplicate key structure is employed, the information management pages will not be deleted even if the duplicates count drops below 201.
2 Re-define the table with the cluster, excluding columns with a high degree of data duplication in index component columns.