An index can be considered to be a copy of a database table that has been reduced to certain fields. This copy is always in sorted form. Sorting provides faster access to the data records of the table, for example using a binary search.
We distinguish between the primary index and the secondary indexes of a table. The primary index consists of the key fields of the table and is automatically created in the database along with the table.
You can also create further indexes on a table in the Java Dictionary. These are called secondary indexes. This is necessary if the table is frequently accessed in a way that does not take advantage of the sorting of the primary index for the access. Different indexes for the same table are distinguished from one another by a separate index name. The system checks that the index name is unique.
Whether or not an index is used for access to a particular table is decided by the database system optimizer. This means that an index might improve performance only with certain database systems. For this reason, too, you can specify in the index definition in the Java Dictionary for which database systems an index is to be created.
All the indexes in the Java Dictionary for a table are normally created on the database when the table itself is created, provided this was not excluded in the index definition for this database system.
If the index fields have a key function, that is, if they already uniquely identify each record of the table, an index can be defined as a unique index.
1. In the maintenance field for the table fields, choose the tab Indexes.
2. Press the pushbutton New (beside the list of index IDs). In the following dialog box, enter the name of the index. Index names, like table names, also have a prefix, followed by an underscore character.
If the name of an index was registered on the name server, it can no longer be deleted.
3. Press the pushbutton New to select the table fields.
4. Now decide whether the index is to be a unique index and whether the index is to be created for all databases. Choose the appropriate checkboxes.
5. Choose (Save All Metadata).