Executor: HiRDB administrator and table owner (or user with DBA privilege)

When changes are made to the processing procedure for a table that contains data, it might develop that some indexes are no longer needed because they are associated with table retrieval conditions that are no longer applicable. If such unneeded indexes are retained, extra processing time is required for them when rows or columns are added or updated. It is more efficient if unneeded indexes are deleted immediately.

You can use HiRDB SQL Tuning Advisor to check unused indexes. For details about how to investigate unused indexes, see the HiRDB Version 9 UAP Development Guide.

The following is the procedure for deleting an index.

Procedure

To delete an index:

1. Use DROP INDEX to delete the index. If a UAP is accessing the table while the index is being deleted, the UAP is placed in lock-release wait status.
2. If necessary, the optimizing information collection utility (pdgetcst command) should be executed after the index has been deleted. Plug-in indexes are not processed by the optimizing information collection utility. For details about whether execution of the optimizing information collection utility is required, see the manual HiRDB Version 9 Command Reference.
3. When an index is deleted, any stored routines that use the deleted index become invalid, and the index information of any stored routines that use the table for which the deleted index is defined become invalid. If this happens, use the ALTER PROCEDURE or ALTER ROUTINE statement to re-create each stored routine.
   In addition, if an index that is used by a trigger is deleted, the trigger becomes invalid. Deleting an index for a table specified in a trigger SQL statement invalidates the index information of that trigger. If this happens, use the ALTER TRIGGER or ALTER ROUTINE statement to re-create the trigger.
   
4. If any index-only global buffer is assigned to the deleted index, delete that global buffer. The procedure explained below is used to delete a global buffer.

Deleting a global buffer

Use one of the following methods to delete a global buffer:

• Terminate HiRDB normally, and then change the pdbuffer operand specification (delete the pdbuffer operand associated with the index that was deleted).
• Use the system reconfiguration command (pdchgconf command) to change the pdbuffer operand specification (delete the pdbuffer operand associated with the index that was deleted). This method eliminates the need to terminate HiRDB normally. Note that HiRDB Advanced High Availability must be installed in order to use this command. For details about changing HiRDB system definitions using the system reconfiguration command, see 9.2 Modifying HiRDB system definitions while HiRDB is running (system reconfiguration command).
• Use the pdbufmod command to delete the global buffer. For details about deleting global buffers with the pdbufmod command, see 9.3 Adding, modifying, and deleting global buffers while HiRDB is running (dynamic updating of global buffers). To use this method to delete global buffers, both of the following conditions must be satisfied:
  HiRDB Advanced High Availability is installed.
  The value Y is specified in the pd_dbbuff_modify operand.
  When you use this method, the information associated with the deleted global buffer becomes invalid if HiRDB is terminated normally or through a planned termination. Therefore, change the pdbuffer operand specification while HiRDB is stopped (delete the pdbuffer operand associated with the index that was deleted). If you do not change the pdbuffer operand specification, an error will occur when HiRDB starts, because the global buffer is not assigned to any index.