Please note : This help page is not for the latest version of Enterprise Architect. The latest help can be found here.
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.
|
|
||||||
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:
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:
|
|
||||||
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:
|
|
||||||
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:
For example:
|
|
||||||
11 |
(On the Columns and Constraints dialog) Click on the Close button.
|
|
Example
In this example, the Order element shows an index:
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 |