You can create multiple indices for a table.
To create an index:
Select a table in the playpen, then click
in the side toolbar. The Index Properties
dialog box appears.
Alternate methods:
Left-click a table in the playpen, then press I.
Right-click a table in the playpen, then click New Index.
Right-click a table in the database tree, then click New Index.
You can enter the following information:
In this field ...
Do this ...
Index Name
Enter a name for the index.
Unique
Select the check box if the index will act as a constraint which guarantees the values in this index's columns are unique across all rows in the table. This is similar to the primary key constraint, with two exceptions: A unique index may contain nullable columns, and a table can have any number of unique indices.
Primary Key
Select the check box to set this index as the table's primary key. The primary key is a special type of index which enforces uniqueness: The values in the primary key's columns are unique across all rows in the table. A table can only have one primary key, and none of the columns in the primary key may be nullable. It is considered good practice to have a primary key on every table in the data model.
Clustered
Select the check box to create a clustered index. Many databases support the notion of a clustered index. The exact meaning varies by platform, but marking an index as clustered often affects the physical ordering of the rows within the table (which may increase or decrease performance based on the types of SQL queries being run). Most database platforms allow only one clustered index per table.
Index Type
Select the index type. The list includes all known index types for all database types configured in your user preferences. If you are building a cross-platform data model, it's best to leave this setting at "platform default." However, if you are tuning your data model for a specific target database, you may choose the desired index type for your platform.
List of columns
Select the In Index check box beside each column you want to include in the index. For each column, select the sort order (Ascending, Descending, or Unspecified).
Use the arrows at the bottom of the dialog box to set the order of the columns within the index. Columns higher in the list will come first in the index's column list.
Notes:
If the table contains columns in the primary key, a separate index will always be created for the primary key column(s), even if you don't select any columns.
On some database platforms, the column order in the index and the column order in the SQL WHERE clause must match in order for the query optimizer to use the index.
On most database platforms, a WHERE clause that references a subset of a multi-column index can usually be used when those columns in the WHERE clause are the leading columns in the index.
Example: Table A has columns B, C, D, E, F. Table A has an index on (F, E, D).
SELECT * FROM a WHERE f='x'; - index can be used on most platforms
SELECT * FROM a WHERE e='x'; - index can not be used on most platforms
SELECT * FROM a WHERE f='x' AND e='x' AND d='x'; - index can be used
SELECT * FROM a WHERE d='x' AND e='x' AND f='x' ; - index can be used on some platforms, but index order and WHERE clause order are different so some platforms will not use the index
Click OK.
To modify an index:
Right-click a table in the playpen, then click Index Properties. If there are multiple indices for the table, select the index you want to modify.
Alternate method:
Right-click the index in the database tree, then click Index Properties.
The Index Properties dialog box appears.
Modify the index properties as required. For a description of the properties, see the section called “Creating an Index” .
Click OK.