Index specifications
The SQL compiler uses index information to optimize queries.
The index information for a data source table is only acquired when the nickname is created for that table. After the nickname is created, any changes to the index on that data source table are not updated on the federated server. When the remote index information changes, you can update the index information stored on the federated server by dropping the nickname for the table and creating the nickname again. Or you can update the index information by running NNSTAT against the nickname. Alternatively, if a new index is added for the data source table, you can define an index specification for the nickname on the federated server.
Index information is not gathered for nicknames on objects that do not have indexes such as views, synonyms, or nonrelational data source objects.
If an object that has a nickname defined for it does not have an index, you can create an index specification for it. Index specifications build an index definition in the global catalog. The index specification is not an actual index. Use the CREATE INDEX statement with the SPECIFICATION ONLY clause to create an index specification. The syntax for creating an index specification on a nickname is similar to the syntax for creating an index on a local table.
Consider creating index specifications when:
- A table acquires a new index.
- You create a nickname for a data source object that does not contain indexes such as a view or a synonym.
When you create an index specification (SPECIFICATION ONLY) on a nickname and specify that the index is unique, the federated database does not verify that the column values in the remote table are unique. If the remote column values are not unique, then queries against the nickname that include that index column might return incorrect data or result in errors.
- If the remote view is a simple SELECT statement on a data source table with an index, creating an index specification on the nickname that matches the index on the data source table can significantly improve query performance.
- If an index specification is created for a remote view that is not a simple SELECT statement (for example, a view created by joining two tables), query performance might suffer.
For example, consider an index specification that is created for a remote view that is a join of two tables. The optimizer can choose that view as the inner element in a nested loop join. The query might have poor performance because the join will be evaluated several times. An alternative is to create nicknames for each of the tables referenced in the data source view and create a federated view that references both nicknames.