13.1.2 Re-evaluating the defined range indexes

(1) Re-evaluating range indexes by checking the SQL statement statistical information

Check the SQL statement statistical information to determine the status of retrieval processing that uses a range index.

Check the SQL statement statistical information by using one of the following methods:

• Executing the adbstat command

  See adbstat (Perform Statistical Analysis of the HADB Server) in the manual HADB Command Reference.

• Checking the SQL trace files

  See (9) SQL statement statistical information in 10.11.2 Information that is output as SQL trace information.

The following procedure explains how to determine the status of retrieval processing that uses a range index by checking the SQL statement statistical information.

Procedure

1. Check chunk skipping to determine the range index's usage status.

   Determine how many times chunks were skipped during retrieval processing that used the range index.

   Check the following items in the SQL statement statistical information:

   • Ridx_chunk_skip_cnt (number of times table chunks were skipped during retrievals that used the range index)

   • Ridx_chunk_judge_cnt (number of times a check was made as to whether a table chunk stored data in the value range satisfying the search condition during retrieval processing that used the range index)

   Based on the checked items, obtain the value resulting from the following formula:

   Formula

2. Check segment skipping to determine the range index's usage status.

   Determine how many times segments were skipped during retrieval processing that used the range index.

   Check the following items in the SQL statement statistical information:

   • Ridx_sgmt_skip_cnt (number of times table segments were skipped during retrievals that used the range index)

   • Ridx_sgmt_judge_cnt (number of times a check was made as to whether a table chunk stored data in the value range satisfying the search condition during retrieval processing that used the range index)

   Based on the checked items, obtain the value resulting from the following formula:

   Formula

3. Re-evaluate the defined range index.

   If the values obtained in steps 1 and 2 are both zero or close to zero, there is no benefit of using the range index (chunks and segments are not being skipped). Therefore, consider deleting the defined range index.

   When an ineffective range index is used, it might take more time to perform retrieval processing than when no range index is used.

   If the values obtained in steps 1 and 2 are both close to 1 or one of them is close to 1, there are benefits of using the range index (chunks or segments are being skipped). Therefore, there is no need to re-evaluate the defined range index.

Important

Perform step 1, even for a single-chunk table. With range indexes, a single-chunk table is treated as one chunk when chunks are skipped. Therefore, you need to check whether the defined range index is effective.

To Page Top

(2) Re-evaluating range indexes by checking the access path statistical information

Check the access path statistical information to determine the status of retrieval processing that uses a range index.

Check the access path statistical 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.

The following procedure explains how to determine the status of retrieval processing that uses a range index by checking the access path statistical information.

Procedure

1. Check chunk skipping to determine the range index's usage status.

   Determine how many times chunks were skipped during retrieval processing that used the range index.

   Check the following items in the access path statistical information:

   • Data_ridx_chunk_skip_cnt (number of times table chunks were skipped during retrievals that used the range index)

   • Data_ridx_chunk_judge_cnt (number of times table chunks were checked during retrievals that used the range index)

   Based on the checked items, obtain the value resulting from the following formula:

   Formula

2. Check segment skipping to determine the range index's usage status.

   Determine how many times segments were skipped during retrieval processing that used the range index.

   Check the following items in the access path statistical information:

   • Data_ridx_sgmt_skip_cnt (number of times table segments were skipped during retrievals that used the range index)

   • Data_ridx_sgmt_judge_cnt (number of times table segments were checked during retrievals that used the range index)

   Based on the checked items, obtain the value resulting from the following formula:

   Formula

3. Re-evaluate the defined range index.

   If the values obtained in steps 1 and 2 are both zero or close to zero, there is no benefit of using the range index (chunks and segments are not being skipped). Therefore, consider deleting the defined range index.

   When an ineffective range index is used, it might take more time to perform retrieval processing than when no range index is used.

   If the values obtained in steps 1 and 2 are both close to 1 or one of them is close to 1, there are benefits of using the range index (chunks or segments are being skipped). Therefore, there is no need to re-evaluate the defined range index.

Important

Perform step 1, even for a single-chunk table. With range indexes, a single-chunk table is treated as one chunk when chunks are skipped. Therefore, you need to check whether the defined range index is effective.

To Page Top