BUILD INDEX (SQL)

Populates one or more indexes with data.

Synopsis

BUILD INDEX [%NOLOCK] [%NOJOURN] FOR TABLE table-name 
  [INDEX index-name [,index-name]] [ KEEP SELECTABILITY ]

BUILD INDEX [%NOLOCK] [%NOJOURN] FOR SCHEMA schema-name [ KEEP SELECTABILITY ]

BUILD INDEX [%NOLOCK] [%NOJOURN] FOR ALL [ KEEP SELECTABILITY ]

Description

BUILD INDEX provides three syntax forms for building/re-building all defined indexes:

Table: BUILD INDEX FOR TABLE table-name. The optional INDEX clause allows you to build/re-build only the specified indexes.
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 indexes for any of the following reasons:

You have used CREATE INDEX to add one or more indexes 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 indexes with data.

BUILD INDEX returns the number of tables modified as the number of Rows Affected.

If you used CREATE INDEX with the DEFER BUILD option to create an index, you must manually build the index. Note that the BUILD INDEX command builds the index’s data, but does not make the index selectable, or usable, in queries. In order to make an index selectable, use the SetMapSelectability()Opens in a new tab method. You can view whether a map is selectable or not in the Management Portal by navigating to System Explorer > SQL > Catalog Details and selecting the Maps/Indices button.

If you specify the KEEP SELECTABILITY option, the newly built index is not automatically marked as selectable. Instead, the index is marked as selectable if and only if it had been marked as selectable before the BUILD INDEX was issued. If the index was marked as not selectable, the index remains not selectable after the index is built.

Classes that were defined through ObjectScript may inherit indexes that need to be built from a superclass. To build these “inherited” indexes, you must call BUILD INDEX on the superclass that defines the index, not on the subclass that uses it.

If a table uses %Storage.SQL, then indexes explicitly defined within the class will not be built.

Privileges

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.

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 indexes 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.

Locking and Journaling

By default, the BUILD INDEX statement acquires an extent lock on each table prior to building its indexes. 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.

Error Codes

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.

Arguments

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

An optional index name or a comma-separated list of index names. If specified, only these indexes are built. If not specified, all indexes defined for the table are built.

FOR SCHEMA schema-name

The name of an existing schema. This command builds all indexes for all tables in the specified schema.