Indexes

If you need to consistently sort a table in a specific order, you can maintain a sorted look-up list - or index - for the table. An index can be:

(For all DBMSs) Column-based, for fast queries on table entries, using the columns specified in the index (the columns having already been defined in the table), or
(For Oracle), function-based, as defined in a behavior expression
 

An index is modeled as a stereotyped operation; on generating DDL, the necessary instructions for generating indexes are written to the DDL output.

Access     (Project Browser or Diagram) | Table context menu | Operations

Create an index

Step

Action

See also

1

On the Operation dialog, add an operation with a name such as IDX_CustomerID.

The IDX_ prefix is optional, but helps to identify the operation.

 

 

2

In the Stereotype field for the operation, select index.

 

 

3

Click on the Save button.

 

 

4

By default, indexes are not created as clustered or unique, and are sorted in ascending order. If this is appropriate for your purposes, go to step 8.

Otherwise, click on the newly-created index operation in the Operations list and click on the Extended Properties button.

The Database Operation Properties dialog displays.

 

 

5

Select the Unique and/or Clustered checkboxes as appropriate.

A Unique index cannot contain more than one instance of a combination of values across a set of columns
Clustered indexes provide improved performance for accessing the columns; you can have one clustered index per table

 

 

6

Set the index sort order by selecting the Ascending or Descending radio button, as appropriate.

 

 

7

Click on the Save & Close button to return to the Operation dialog.

 

 

8

If you prefer (and if the DBMS you are using supports it), you can type an index comment in the Notes field of the Operation dialog.

 

 

9

It is not necessary to add any columns to an Oracle function-based index.

To model function-based indexes, click on the index operation in the Operations list and click on the  Behavior page in the Properties list in the left hand panel of the dialog.

In the Initial Code field, type the index behavior expression, and click on the Save button.

Go to step 11.

 

 

10

To add columns to a column-based index, click on the index operation in the Operations list and click on the Column page in the Properties list in the left hand panel of the dialog.

Select the required columns from the Column Name drop-down list, in the required order. Click on the Save button after selecting each column from the drop-down list.

If you prefer, you can adjust the sequence of columns using the 'up hand' and 'down hand' buttons.

 

 

11

Click on the Close button.

 

 

Example

In the example below, the Order element shows what an index looks like in a diagram:

IndexesTriggers

Learn more

Learning Center topics

(Alt+F1) | Database Engineering | Physical Data Model | Add Index