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.
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.
Classes that were defined through ObjectScript may inherit indices
that need to be built from a superclass. To build these “inherited”
indices, 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.
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'
If the specified schema-name does
not exist, InterSystems IRIS issues an SQLCODE -473 error and sets
%msg to Schema 'sample' not found.