This topic describes the action that the federated server takes for Informix synonyms based on a table or on a view:
Before you begin
Restrictions
About this task
Procedure
The following examples describe how to create an index specification on a nickname that corresponds to an Informix synonym.
Example: A nickname is created on an Informix synonym that is based on a table
When the synonym is based on an Informix table that does not contain an index, you can create an index specification for the synonym to tell the optimizer which column or columns to search on to find data quickly. The statement you create will specify the nickname for the synonym, and you will supply information about the column or columns in the table that the synonym is based on.
In this example, you create the nickname CONTRACTS for a synonym called SALES_CONTRACTS. The table that this synonym is based on is called SALES2006_TABLE and contains several indexes: REGION, AMOUNT, SALES_REP. The CREATE INDEX statement you create will reference the nickname for the synonym and contain information about the index of the underlying table for the synonym.
CREATE UNIQUE INDEX NORTHWEST_2006_REGION ON CONTRACTS (REGION) SPECIFICATION ONLY
where NORTHWEST_2006_REGION is the index name and CONTRACTS is the nickname for the synonym SALES_CONTRACTS.
Example: A nickname is created on an Informix synonym that is based on a view
You create the nickname JP_SALES2007 for a synonym based on 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 that you create will reference the nickname for the synonym and contain information about the index of the underlying table for the view.
When creating an index specification for a synonym based on a view, make certain that the column or columns 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.
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.