Indexes

An index on a table is used to improve the performance of lookups and sort operations on the table at the expense of additional storage. 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. or
(For SQL Server), one or more columns in addition to the specified column, and a fill factor
 

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 | Right-click Table element | Operations
Diagram | Right-click Table element | Features and Properties | 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, click on the drop-down arrow and 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 12.

 

 

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

For SQL Server, to add included columns select the Tagged Values page from the Properties list and add the Tagged Values:

INCLUDE, and in the Value field type the column names as a comma-separated list
FILLFACTOR, and in the Value field type the numerical fill factor
 

For example:

            IndexTaggedValue

 

 

12

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) | Enterprise Architect | Database Engineering | Physical Data Model | Add Index