11.3.2 Rebuilding B-tree indexes
If you have performed row insertion, row updating, and row deletion operations frequently on a table for which B-tree indexes have been defined, consider rebuilding the B-tree indexes.
You can expect the following benefits from rebuilding B-tree indexes:
-
Prevention of decreases in the performance of retrieval processing when the B-tree indexes are used.
-
Reduction of the amount of space used by data DB areas (the data for invalid B-tree indexes resulting from row updating and deletion operations can be deleted).
-
Suppression of index page splits (unused areas in index pages for B-tree indexes can be reallocated).
To rebuild B-tree indexes, execute the adbidxrebuild command. For details about the adbidxrebuild command, see adbidxrebuild (Rebuild Indexes) in the manual HADB Command Reference.
- Note
-
-
To rebuild a B-tree index that is in unfinished status, see 15.9.1 Steps to take when unfinished status is applied to a B-tree index.
-
If you rebuilt a unique index that violated the uniqueness constraint, see 15.9.2 Steps to take when the uniqueness constraint is violated (when the KFAA61205-W message is output).
-
The following subsections provide guidelines for determining whether B-tree indexes need to be rebuilt.
- Organization of this subsection
(1) Prevention of decreases in the performance of retrieval processing when B-tree indexes are used
If row insertion, updating, or deletion operations are performed frequently on a table for which B-tree indexes have been defined, the performance of retrieval processing using those B-tree indexes decreases.
If this is the case, check one of the output items listed below. If the value of the output item is large, rebuild the B-tree indexes. By rebuilding B-tree indexes, you can prevent deterioration in the performance of retrieval processing when B-tree indexes are used.
-
Value of Bidx_validation_check_cnt that is output as the SQL statement statistical information (number of times row data validity was checked during retrievals using the B-tree indexes)
The SQL statement statistical information is output to both the statistics log files and the SQL trace files.
To check the SQL statement statistical information that has been output to the statistics log files, execute the adbstat command. For details about the adbstat command, see adbstat (Perform Statistical Analysis of the HADB Server) in the manual HADB Command Reference.
To check the SQL statement statistical information that has been output to the SQL trace files, check the SQL trace information in the SQL trace files. For details about SQL trace information, see (9) SQL statement statistical information in 10.11.2 Information that is output as SQL trace information.
-
Value of Data_bidx_validation_check_cnt that is output as the data access information in the access path statistical information (number of times row validity was checked during retrievals using the B-tree indexes)
The access path statistical information is output to the SQL trace files. Check the SQL trace information in the SQL trace files. For details about access path statistical information, see 10.11.3 Examples of output of and output items for access path statistical information.
If you can check both the SQL statement statistical information and the access path statistical information, we recommend that you check the access path statistical information. Checking the access path statistical information enables you to identify the tables whose B-tree indexes need to be rebuilt when you are retrieving data from multiple tables.
(2) Reduction of the amount of space used by data DB areas
If you have performed row updating or deletion operations frequently on a table for which B-tree indexes have been defined, use the following procedure to determine whether the B-tree indexes need to be rebuilt.
Procedure
-
Use the adbdbstatus command to check the amount of space used by the B-tree indexes.
Use the adbdbstatus command to output index summary information. Then, check the output value for Used_segments (number of segments used by indexes). For details about the adbdbstatus command, see adbdbstatus (Analyze the Database Status) in the manual HADB Command Reference.
-
Check the sizes of the B-tree indexes that had been estimated based on the valid data in the base table.
For details about how to determine the sizes of B-tree indexes, see the following subsections in (2) Explanation of variables under 5.8.1 Determining the total number of pages in the data DB area. Note that the subsection to see differs depending on the type of the table.
-
Single-chunk table
(c) Determining the variable SGIDX (for a single-chunk table)
-
Multi-chunk table
(h) Determining the variable SGIDX (for a multi-chunk table)
Note that the variable SGIDX determines the sum of all B-tree indexes stored in the data DB area. In this step, obtain the size of only the target B-tree indexes.
-
Compare the results of steps 1 and 2. If their difference is large, rebuild the B-tree indexes.
(3) Suppression of index page splits in B-tree indexes
If row insertion or updating operations are performed frequently on a table for which B-tree indexes have been defined, index page splits might be occurring.
To determine whether index page splits have occurred, check Bidx_page_split_cnt (number of times index page splits occurred in B-tree indexes) in the SQL statement statistical information. If index page splits have occurred, rebuild the B-tree indexes. Rebuilding B-tree indexes enables you to suppress index page splits because unused areas in the index pages can be reallocated.
The SQL statement statistical information is output to the statistics log files and the SQL trace files.
To check the SQL statement statistical information that has been output to the statistics log files, execute the adbstat command. For details about the adbstat command, see adbstat (Perform Statistical Analysis of the HADB Server) in the manual HADB Command Reference.
To check the SQL statement statistical information that has been output to the SQL trace files, check the SQL trace information in the SQL trace files. For details about SQL trace information, see (9) SQL statement statistical information in 10.11.2 Information that is output as SQL trace information.