Creating index specifications on views

When a nickname is created for a view, the federated server is unaware of the underlying table (and its indexes) from which the view was generated. Create an index specification for the view so that the SQL Compiler can use this information when processing queries that reference the view.

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

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

  • Ensure that the column or columns that the table index is based on is part of the view.
  • If you want to create index specifications for all indexes on the underlying table, each index specification must be created separately.

Example

Example: Create an index specification that describes the REGION index

Suppose that you create the nickname JP_SALES2007 for a view called JAPAN_SALES2007. The underlying table for this view is the JAPAN_SALES table which contains several indexes: REGION, AMOUNT, SALES_REP. The CREATE INDEX statement you create will reference the nickname for the view and contain information about the index of the underlying table for the view. 
CREATE UNIQUE INDEX JP_2007_REGION ON JP_SALES2007  
(REGION) SPECIFICATION ONLY
where JP_2007_REGION is the index name, and JP_SALES2007 is the nickname for the view JAPAN_SALES2007.