This is the fourth day of my participation in the September Challenge.

Chapter 5 SQL command BUILD INDEX

Populate one or more indexes with data.

The outline

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
Copy the code

parameter

  • FOR TABLE table-name– Name of an existing table.

Table names can be qualified (schema.table) or unqualified (table). Unqualified table names accept the default schema name.

  • INDEX index-name– Optional – Index names or 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– Name of an existing schema.

This command builds all indexes for all tables in the specified schema.

describe

BUILD INDEX provides three syntactic forms to BUILD/rebuild all defined indexes:

  • Table: BUILD INDEX FOR TABLE table-name.The optional INDEX clause allows only the specified INDEX to be built/rebuilt.
  • All tables in the schema:BUILD INDEX FOR schema schema-name
  • All tables in the current namespace:BUILD INDEX FOR All

You might want to build an index for any of the following reasons:

  • Have been usingCREATE INDEXOne or more indexes are added to a table that already contains data.
  • Have been using%NOINDEXThe option is executed on the tableINSERT,UPDATEorDELETEOperations, rather than accepting the performance overhead of writing each operation to an index.

In both cases, you can populate these indexes with data using BUILD INDEX.

BUILD INDEX returns the number of modified tables as the number of affected rows.

permissions

The BUILD INDEX command is a privileged operation. The user must have the %BUILD_INDEX administration permission to execute BUILD INDEX. If you do not do so, a SQLCODE -99 error will occur with % MSG, User ‘name’ does not have %BUILD_INDEX PRIVILEGES. If you have the appropriate GRANT permissions, you can use the GRANT command to assign %BUILD_INDEX permissions to users or roles. Administrative permissions are namespace-specific.

The user must have SELECT permission on the specified table. If the user is the Owner(creator) of the table, the SELECT permission on the table is automatically granted to the user. Otherwise, the user must be granted SELECT privileges on the table.

  • Execute on the specified tableBUILD INDEX FOR TABLEwithoutSELECTPermissions will result inSQLCODE -30Wrong, and%msg Table 'name' not found.
  • aBUILD INDEX FOR SCHEMAWill only be available to the userSELECTBuild indexes for the table of permissions.

If the user does not have SELECT permission on any of the tables in the schema, this command completes without error and does not affect line 0.

You can determine whether the current user has SELECT privileges by calling the %CHECKPRIV command. You can use the GRANT command to assign SELECT privileges to a specified table.

Lock and log

By default, the BUILD INDEX statement obtains a range lock for each table before building the INDEX. This prevents other processes from modifying the table data. The lock is automatically released at the end of the BUILD INDEX operation. You can specify %NOLOCK to prevent table locking.

By default, the BUILD INDEX statement uses the logging Settings for the current process. You can specify % NOJOURN to prevent logging.

The error code

  • If the specified table name does not exist, IRIS issues oneSQLCODE -30The error and will%msgSet toTable 'sample.tname' does not exist. If a view is specified instead of a table, or the specified table does not have oneSELECTPermission, this error message is returned.
  • IRIS emits if the specified index name does not existSQLCODE -400The error and will%msgSet to ERROR #5066: Index name 'sample.tname::badindex' is invalid.
  • IRIS emits if the specified index name does not existSQLCODE -400The error and will%msgSet toSchema 'sample' not found.