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; collectively, operations defined against a Table are referred to as constraints. When generating DDL, the necessary instructions for generating indexes are written to the DDL output.

Access     On Diagram or in Project Browser | Right-click Table element | Features &Properties | Operations > Constraints | Right-click on list | Add New Constraint  (Ctrl+N)

Work on an index

Step

Action

See also

1

On the Constraints tab of the Columns and Constraints dialog, the new constraint is added with the default name constraint1 and the Type of index.

Overtype the name with your preferred index name.

 

 

2

If you prefer, type an alias for the constraint, in the Alias field.

The Columns field is read-only; it is populated with the columns that you assign to the Involved Columns tab (step 4).

 

 

3

In the Property panel, review the automatically-listed properties of the index.

An index can have several properties, depending on the Database Management System defined in the Table element.  The most common are Is Unique and Is Clustered, both of which default to False.

·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

 

 

4

The Available Columns panel lists the columns defined for the Table (on the Columns tab).

For each column to assign to the index, either:

·Right-click on the column name and select Assign column '<name>' or
·Press Ctrl+left arrow or
·Double-click on the column name

 

The column name is transferred to the Involved Columns tab.

 

 

5

If you do not want the assigned column in the Involved Columns tab, either:

·Right-click on it and select Unassign column '<name>', or
·Press Ctrl+right arrow, or
·Double-click on the column name

 

 

6

In the Order field, click on the drop-down arrow and select either Ascending or Descending, to set the appropriate sort order within the column.

 

 

7

If you have a number of columns in the index, you can rearrange the sequence by right-clicking on the column to move and selecting either:

·Move column '<name>' up   (Ctrl+Up arrow) or
·Move column '<name>' down   (Ctrl+Down arrow)

 

 

8

If necessary (and if the DBMS you are using supports it), you can type a formatted index comment in the field on the Notes tab.

 

 

9

To model an Oracle function-based index, do not assign any columns (step 4) to the index. Instead, select the Functional-based property and type in both the function and the column involved. For example:

 

MakeProperCase(Column1)

 

If the function is long, click on the expansion button ( ... ) at the end of the field and type the function into the Function-based index for constraint <table name> dialog.

 

 

10

For SQL Server, there are two additional properties for Indexes shown in the Properties list:

·FILLFACTOR refines the index data storage and performance; it is a numeric value between 1 and 100 that indicates the percentage of available space that should be used for data
·INCLUDE identifies additional columns that are considered as non-key columns; where multiple columns are specified, separate the column names with commas
 

For example:

            IndexTaggedValue

 

 

11

(On the Columns and Constraints dialog) Click on the Close button.

 

 

Example

In this example, the Order element shows an index:

IndexesTriggers

Delete an Index

If you do not want to keep an index, either:

·Right-click on it in the list and select Delete constraint '<name>', or
·Click on the item and press Ctrl+D

 

The index is immediately deleted.

Learn more

Learning Center topics

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