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; 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.
|
|
||||
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:
For example:
|
|
||||
12 |
Click on the Close button.
|
|
Example
In the example below, the Order element shows what an index looks like in a diagram:
Learn more
Learning Center topics
| • | (Alt+F1) | Enterprise Architect | Database Engineering | Physical Data Model | Add Index |
