11.3.3 Rebuilding text indexes

(1) Prevention of decreases in the performance of retrieval processing using when text indexes are used

If row insertion, updating, or deletion operations are performed frequently on a table for which text indexes have been defined, the performance of retrieval processing using those text indexes decreases.

If you have updated (by performing row insertion and updating operations) 0.01% or more of the data stored in a table for which text indexes have been defined, consider rebuilding the text indexes.

Similarly, if you have deleted (by performing row deletion operations) 30% or more of the data stored in a table for which text indexes have been defined, consider rebuilding the text indexes.

Rebuilding text indexes enables you to prevent deterioration in the performance of retrieval processing using those text indexes.

To Page Top

(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 text indexes have been defined, use the following procedure to determine whether the text indexes need to be rebuilt.

Procedure

1. Use the adbdbstatus command to check the amount of space used by the text 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.

2. Check the sizes of the text indexes that had been estimated based on the valid data in the base table.

   For details about how to determine the sizes of text 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

     (e) Determining the variable SGTIX (for a single-chunk table)

   • Multi-chunk table

     (j) Determining the variable SGTIX (for a multi-chunk table)

   Note that the formula for the variable SGTIX obtains the sum of the values for all text indexes stored in the data DB area. In this step, obtain the value only for the target text indexes.

Compare the results of steps 1 and 2. If their difference is large, rebuild the text indexes.

To Page Top

(3) Suppression of index page splits in text indexes

If row insertion or update operations are performed frequently on a table for which text indexes have been defined, index page splits might be occurring.

To determine whether index page splits have occurred, check Tidx_page_split_cnt (number of times index page split occurred in text indexes) in the SQL statement statistical information. If index page splits have occurred, rebuild the text indexes. Rebuilding text indexes enables you to suppress index page splits because unused areas in the text 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.

To Page Top