Adding Indexes to a Synonym

You can create indexes in a synonym by adding them in the Synonym Editor. When you create a table using Data Management, Recreate DBMS table, the indexes are created in the relational data base.

When you use New, Synonym to create a synonym for a relational database table with more indexes than just the primary key, they will be included in the synonym.

Additional indexes are useful when you anticipate using a column in a JOIN operation, a WHERE condition, or as an argument to DB_LOOKUP. You can add multiple indexes. Each index can be on one or more columns. You can specify if the index is sorted, and whether it is a unique index.


Top of page

Example: Adding an Index to a Synonym

This example uses the sample synonym ibisamp/dmreps that has a primary key on column REPNO and a unique index on that column. It shows how to add an index on the column EMPID, and another index on the FNAME and LNAME columns that does not have to be unique.

  1. From the ibisamp application directory, double-click the dmreps synonym to open it in the Synonym Editor.
  2. Right-click the segment name DMREPS, select Insert, and then click Index.

    The Index Fields Editor dialog box opens. The field REPNO is already selected because it is the key column of the table.

  3. Click OK.
  4. A new folder named Indexes appears in the tree with Index1 defined for REPNO. Right-click the Indexes folder, select Insert, and then click Index.

    The Index Fields Editor dialog box opens.

  5. Double-click EMPID in the Available Fields pane to add it to the Selected Field(s) pane, as shown in the following image.

    Index Fields Editor dialog box

  6. Click OK.
  7. Right-click the Indexes folder, select Insert, and then click Index.

    The Index Fields Editor dialog box opens.

  8. Double-click FNAME and LNAME to add them to the Selected Field(s) pane.
  9. Click OK.
  10. In the Properties page for Index3, select N – Index is not unique in the INDEX_UNIQUE drop-down menu.

    Your synonym should look like the following image.

    Synonym


iWay Software