REINDEX DATABASE

Use the REINDEX DATABASE command to vacuum and reindex a user database. You must connect to the SYSTEM database as the admin user to run this command.

When you create, use, and delete SQL objects in a user database, the database software creates entries in the system catalog for those objects. The system catalog tables contain indexes to accelerate the catalog access. If the database users create, use, and delete objects frequently, which often happens by using third-party SQL reporting applications, the index entries for the deleted objects can grow very large and impact query performance.

You can use the REINDEX DATABASE command to remove (or vacuum) entries for deleted objects within the catalog tables, and then recreate the catalog table indexes within the user database. You must have permission to connect to the SYSTEM database as the admin user to run the command. The user database cannot have any active sessions or connections. The NPS system can remain online while the REINDEX DATABASE command runs. The reindexing process is usually very fast. The command typically requires a few seconds to update a small user database, and very large databases could require a few minutes to complete.

Syntax

Syntax for the REINDEX DATABASE command:

REINDEX DATABASE <name>

Input

The REINDEX DATABASE command takes the following input:

Table 1. REINDEX DATABASE input
Input	Description
<name>	The name of the user database that you want to reindex.

Outputs

The REINDEX DATABASE command has the following outputs:

Table 2. REINDEX DATABASE outputs
Output	Description
`REINDEX DATABASE`	The command was successful.
`ERROR: command must be run from the system database`	You must connect to the SYSTEM database as the admin user to run the command.
`ERROR: unable to acquire lock on database name`	The database has active connections. You must stop the sessions to the database before you can reindex the database. You cannot reindex the SYSTEM database.
`ERROR: REINDEX DATABASE cannot run inside a BEGIN/END block or stored procedure`	You cannot use the REINDEX DATABASE command inside the body of a stored procedure or within a transaction block.
`ERROR: REINDEX DATABASE: permission denied`	You must connect as the admin user to the SYSTEM database to run the command.

Usage

The following provides sample usage:

Reindex the database emp:

SYSTEM.ADMIN(ADMIN)=> REINDEX DATABASE emp;
REINDEX DATABASE