Creating index specifications on tables that acquire new indexes

For situations in which a table acquires a new index, you should create an index specification on the nickname that corresponds to the table.

Before you begin

The privileges held by the authorization ID of the statement must include at least one of the following:
  • SYSADM or DBADM authority
  • One of CONTROL privilege on the object or INDEX privilege on the object. And one of IMPLICIT_SCHEMA authority on the database, if the implicit or explicit schema name of the index does not exist, or CREATEIN privilege on the schema, if the schema name of the index refers to an existing schema.

About this task

There are several situations in which a table acquires a new index:
  • You create a nickname for a table that does not have an index, but acquires an index later
  • You create a nickname for a table that has an index, but acquires another index later
In these situations, you should create an index specification for the table so that the SQL Complier can use this information when processing queries that reference the table.

Restrictions

There are some restrictions when creating an index on a nickname.
  • If the bind option DYNAMICRULES BIND applies, the statement cannot be dynamically prepared. Also, you cannot use the INCLUDE, CLUSTER, PCTFREE, MINPCTUSED, DISALLOW REVERSE SCANS, and ALLOW REVERSE SCANS parameters in the CREATE INDEX statement.
  • UNIQUE should be specified only if the data for the index key contains unique values for every row of the data source table. The uniqueness will not be checked.
  • The sum of the stored lengths of the specified columns must not be greater than 1024.
  • No LOB column or distinct type column based on a LOB can be used as part of an index. This restriction is enforced even if the length attribute of the column is small enough to fit within the 1024-byte limit.

Procedure

The following examples describe how to create an index specification for a nickname that corresponds to a table that acquires an index.

Example: A table that has no index, later acquires an index

Suppose that you create the nickname EMPLOYEE for a data source table called CURRENT_EMP, which has no indexes. Sometime after this nickname is created, an index was defined on CURRENT_EMP using the WORKDEPT and JOB columns for the index key.

To create an index specification that describes this index, the syntax would be: 
CREATE UNIQUE INDEX JOB_BY_DEPT ON EMPLOYEE  
(WORKDEPT, JOB) SPECIFICATION ONLY
where JOB_BY_DEPT is the index name.

Example: A table acquires a new index

Suppose that you create the nickname JP_SALES for a table called JAPAN_SALES. A new index is later added to the table in addition to the ones it had when the nickname was created. The new index uses the MARKUP column for the index key.

To create an index specification that describes this index, the syntax would be: 
CREATE UNIQUE INDEX JP_MARKUP ON JP_SALES (MARKUP) SPECIFICATION ONLY
where JP_MARKUP is the index name.