BUILD INDEX (SQL)
BUILD INDEX [%NOLOCK] [%NOJOURN] FOR TABLE table-name [INDEX index-name [,index-name]] BUILD INDEX [%NOLOCK] [%NOJOURN] FOR SCHEMA schema-name BUILD INDEX [%NOLOCK] [%NOJOURN] FOR ALL
|FOR TABLE table-name||The name of an existing table. A table-name can be qualified (schema.table), or unqualified (table). An unqualified table name takes the default schema name.|
|INDEX index-name||Optional — An index name or a comma-separated list of index names. If specified, only these indices are built. If not specified, all indices defined for the table are built.|
|FOR SCHEMA schema-name||The name of an existing schema. This command builds all indices for all tables in the specified schema.|
BUILD INDEX provides three syntax forms for building/re-building all defined indices:
Table: BUILD INDEX FOR TABLE table-name. The optional INDEX clause allows you to build/re-build only the specified indices.
All tables in a schema: BUILD INDEX FOR SCHEMA schema-name
All tables in the current namespace: BUILD INDEX FOR ALL
You may wish to build indices for any of the following reasons:
You have used CREATE INDEX to add one or more indices to a table that already contains data.
You have performed INSERT, UPDATE, or DELETE operations on a table using the %NOINDEX option, rather than accepting the performance overhead of having each of these operations write to the index.
In either case, use BUILD INDEX to populate these indices with data.
BUILD INDEX returns the number of tables modified as the number of Rows Affected.
The BUILD INDEX command is a privileged operation. The user must have %BUILD_INDEX administrative privilege to execute BUILD INDEX. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %BUILD_INDEX privileges. You can use the GRANT command to assign %BUILD_INDEX privileges to a user or role, if you hold appropriate granting privileges. Administrative privileges are namespace-specific. For further details, refer to Privileges in Using InterSystems SQL.
The user must have SELECT privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted SELECT privilege for that table. Otherwise, the user must be granted SELECT privilege for the table.
Issuing BUILD INDEX FOR TABLE without SELECT privilege on the specified table results in an SQLCODE –30 error with the %msg Table 'name' not found.
Issuing BUILD INDEX FOR SCHEMA only builds indices for those table for which the user has SELECT privilege. If the user does not have SELECT privilege for any tables in the schema, the command completes without error, with 0 rows affected.
You can determine if the current user has SELECT privilege by invoking the %CHECKPRIV command. You can use the GRANT command to assign SELECT privilege to a specified table. For further details, refer to Privileges in Using InterSystems SQL.
Locking and Journaling
By default, the BUILD INDEX statement acquires an extent lock on each table prior to building its indices. This prevents other processes from modifying the table’s data. This lock is automatically released at the conclusion of the BUILD INDEX operation. You can specify %NOLOCK to prevent table locking.
By default, the BUILD INDEX statement uses the journaling setting for the current process. You can specify %NOJOURN to prevent journaling.
To use %NOLOCK or %NOJOURN, you must have the corresponding SQL administrative privilege, which you can set by using the GRANT command.
If the specified table-name does not exist, InterSystems IRIS issues an SQLCODE -30 error and sets %msg to Table 'sample.tname' does not exist. This error message is returned if you specify a view rather than a table, or if you specify a table for which you do not have SELECT privilege.
If the specified index-name does not exist, InterSystems IRIS issues an SQLCODE -400 error and sets %msg to ERROR #5066: Index name 'sample.tname::badindex' is invalid.
If the specified schema-name does not exist, InterSystems IRIS issues an SQLCODE -473 error and sets %msg to Schema 'sample' not found.
“Defining and Building Indices” chapter in SQL Optimization Guide
SQLCODE error messages listed in the InterSystems IRIS Error Reference