Routinely Recreating an Index

Context

When data deletion is repeatedly performed in the database, index keys will be deleted from the index page, resulting in index distention. Recreating an index routinely improves query efficiency.

The database supports B-tree, GIN, and psort indexes.

• Recreating a B-tree index helps improve query efficiency.
  • If massive data is deleted, index keys on the index page will be deleted. As a result, the number of index pages reduces and index bloat occurs. Recreating an index helps reclaim wasted space.
  • In the created index, pages adjacent in its logical structure are adjacent in its physical structure. Therefore, a created index achieves higher access speed than an index that has been updated for multiple times.
• You are advised not to recreate a non-B-tree index.

Rebuilding an Index

Use either of the following two methods to recreate an index:

• Run the DROP INDEX statement to delete an index and run the CREATE INDEX statement to create an index.

  When you delete an index, a temporary exclusive lock is added in the parent table to block related read/write operations. When you create an index, the write operation is locked but the read operation is not. The data is read and scanned by order.

• Run the REINDEX statement to recreate an index:
  • When you run the REINDEX TABLE statement to recreate an index, an exclusive lock is added to block related read/write operations.
  • When you run the REINDEX INTERNAL TABLE statement to recreate an index for a desc table (), an exclusive lock is added to block read/write operations on the table.

Procedure