Database Indexes

Ribbon

Develop > Data Modeling > Database Builder > Click on Table name > Constraints/Indexes

Context Menu

In diagram | Right-click on Table | Features | Constraints/Indexes

Keyboard Shortcuts

Click on Table: F9 > Constraints/Indexes

1

On the 'Constraints/Indexes' tab for the Table, right-click and select 'Add new constraint'.

The new constraint is added with the default name 'constraint1' and the Type of 'index'.

Overtype the name with your preferred index name.

2

Assign the appropriate columns to the Index.

The 'Assigned Columns' list has an additional 'Order' field that specifies the order (Ascending or Descending) in which each assigned column is stored in the index. You can toggle the order for each column, as required.

Additionally, for MySQL indexes, a 'Len' field will be visible in which you can define Partial Indexes; that is, an index that uses the leading 'n' number of characters of a text based field. The 'Len' field takes only whole number numeric values of between 0 and the column's defined length. A value of 0 (which is the default) indicates that the entire column is to be indexed.

3

In the 'Property' panel, review the settings of the extended properties that are defined for the current DBMS.

Is Unique

(True / False) indicates whether the current index is a 'Unique Index'. A Unique Index ensures that the indexed column (or columns) does not contain duplicate values, thereby ensuring that each row has a unique value (or combination of values when the index consists of multiple columns).

Is Clustered

(True / False) indicates whether the current index is a 'Clustered Index'.  With a clustered index, the rows of the table are physically stored in the same order as in the index, therefore there can be only one clustered index per table.  By default a table's Primary Key is clustered. 

Not all DBMS's support clustered indexes, therefore the 'Is Clustered' Index property will only be visible for DBMSs that support it.

Is Bitmap

(True / False) indicates whether the current index is a 'Bitmap' index.  Bitmap indexes are meant to be used on columns that have relatively few unique values (referred to as 'low cardinality' columns) and that physically consist of a bit array (commonly called bitmaps) for each unique value. Each of the arrays will have a bit for each row in the table. 

Consider this example: a bitmap index is created on a column called 'Gender', which has the options 'Male' or 'Female'. Physically, the index will consist of two bit arrays, one for 'Male' and one for 'Female'.  The female bit array will have a 1 in each bit where the matching row has the value 'Female'.

The 'Is Bitmap' and 'Is Unique' properties are mutually exclusive, and so the DDL generation will ignore the 'Is Unique' property when the 'Is Bitmap' property is True.

Bitmap Indexes are only supported by Oracle; therefore, this property is only visible while modeling Oracle indexes.

Fill Factor

A numeric value between 0 and 100, that defines the percentage of available space that should be used for data.

Not all DBMSs support fill factor, therefore the 'Fill Factor' index property will only be visible for DBMSs that support it.

Functional-based

A SQL statement that defines the function/statement that will be evaluated and the results indexed; for example:

     LOWER("field")

Not all DBMSs support functional-based indexes, therefore the 'Functional-based' Index property will only be visible for DBMSs that support them, such as PostgreSQL and Oracle.

Include

Identifies a comma-separated list (CSV) of non-key Columns from the current table.

Not all DBMSs support the 'Include' property on indexes, therefore this property will only be visible for DBMSs that support it.