Index specifications in a federated system

In a federated system, you use the CREATE INDEX statement with a nickname to store information in the global catalog about the availability of an index on the remote object. The query optimizer uses this information to optimize queries.

When you issue a CREATE INDEX statement
  • If a nickname is created for a table, the CREATE INDEX statement collects index information about the index that was created on the remote table.
  • If a nickname is created for a view, the CREATE INDEX statement references the nickname for the view and contains information about the index on the table underlying the view.
The index specification tells the federated server about the columns and their uniqueness properties that comprise a remote index. It does not tell the federated server about the statistical properties of the index, such as, the number of unique values of the index key.

You do not need to supply index specifications if the remote index was in place at the time that the nickname was created.

A federated server does not create an index specification when you create a nickname for:
  • A table that has no indexes
  • A view, which typically does not have any index information stored in the remote catalog
  • A data source object that does not have a remote catalog from which the federated server can obtain the index information

Suppose that a table acquires a new index, in addition to the ones it had when the nickname was created. Because index information is supplied to the global catalog at the time the nickname is created, the federated server is unaware of the new index. Similarly, 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. In these circumstances, you can supply the necessary index information to the global catalog. You can create an index specification for tables that have no indexes. The index specification tells the query optimizer which column or columns in the table to search on to find data quickly.

Use index specifications with relational data sources. Creating an index specification for a nonrelational data source will not improve performance.