11.3.5 Rebuilding range indexes
If you have performed row insertion, row updating, and row deletion operations frequently on a table for which range indexes have been defined, consider rebuilding the range indexes.
You can expect the following benefits from rebuilding range indexes:
-
Prevention of decreases in the performance of retrieval processing when the range indexes are used.
-
Reduction of the amount of space used by data DB areas (the data for invalid range indexes resulting from row updating and deletion operations can be deleted).
To rebuild range 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 range index that is in unfinished status, see 15.11.1 Steps to take when unfinished status is applied to a range index.
The following subsections provide guidelines for determining whether range indexes need to be rebuilt.
- Organization of this subsection
(1) Prevention of decreases in the performance of retrieval processing when range indexes are used
If row insertion, update, or deletion operations are performed frequently on a table for which range indexes have been defined, the performance of retrieval processing using those range indexes decreases.
To check the status of retrieval processing when range indexes are used, see 13.1.2 Re-evaluating the defined range indexes. Check either the SQL statement statistical information or the access path statistical information to determine the usage status of the range indexes. If the range indexes are providing no benefit (the calculation result is 0 or close to 0), rebuild the range indexes.
(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 range indexes have been defined, use the following procedure to determine whether the range indexes need to be rebuild.
Procedure
-
Use the adbdbstatus command to check the amount of space used by the range 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 range indexes that had been estimated based on the valid data in the base table.
For details about how to determine the sizes of range 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
(d) Determining the variable SGRIX (for a single-chunk table)
-
Multi-chunk table
(i) Determining the variable SGRIX (for a multi-chunk table)
Note that the formula for the variable SGRIX obtains the sum of the values for all range indexes stored in the data DB area. In this step, obtain the value only for the target range indexes.
-
Compare the results of steps 1 and 2. If their difference is large, rebuild the range indexes.