Creating an index

Chapter 3 Building Physical Diagrams

Creating an index

You can create the following types of index:

A user-defined index - Associated with one or more columns
An index linked to a key - Automatically updated when the key column or columns are modified. An index linked to a key is unique because it uses the same unique set of columns as the key.
A function-based index - [if supported by the DBMS] Precomputes the value of a function or expression based on one or more columns and stores it in the index. The function or the expression will replace the index column in the index definition. Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses.

Index naming conventions

Use the following naming conventions for indexes:

Index	Naming convention
Primary key	Table code followed by PK; for example EMPLOYEE _PK
Foreign key	Table code followed by FK; for example PROJECT _ FK
Alternate key	Table code followed by AK; for example EMPLOYEE _ AK

Example

A table contains a compound primary key. This is a primary key designated to more than one column in a table. You create an index and link it to the primary key. If one of the primary key columns is deleted, the corresponding index associated with the column is also deleted.

To create an index:

Double-click a table symbol to display its property sheet and click the Indexes tab.

Click the Add a Row tool and enter an index name and an index code.

Click the Properties tool to open the property sheet of the new index.

Type or select any appropriate index properties, and then click the Columns tab.

To create a user defined index: click the Add Columns tool, select one or more columns from the list, and then click OK

To create an index linked to a key: select the primary key, an alternate key, or foreign key from the Columns definition list

To create a function-based index [if supported by the DBMS]: click the Add a Row tool, then click in the Expression column and select the ellipsis button to open the SQL Editor. Enter an expression in the editor and then click OK

Select Ascending or Descending in the Sort column.

Click OK in each of the dialog boxes.

Reverse engineering function-based index

An index column with an expression has a LONG data type that cannot be concatenated in a string statement during reverse engineering. The only way to bypass this limitation and concatenate this value is to use variables in the query executed to retrieve the adequate information.

In the Oracle 8i and Oracle 8i2 DBMS, the query SqlListQuery defined in the Index category contains the following variable used to recover the index expression in a column with the LONG data type.

'%SqlExpression.Xpr'||i.table_name||i.index_name||c.column_position||'%'

For more information on the use of variables in reverse engineering queries, see section Extension mechanism for live database reverse engineering queries, in the DBMS Reference Guide chapter of the Advanced User Documentation .

Example

Function-based indexes defined on UPPER(column_name) or LOWER(column_name) can facilitate case-insensitive searches.

You want to define an index that will put all names in lowercase on the table EMPLOYEE in order to ease search. You can define the following index (syntax for Oracle 8i):

CREATE INDEX low_name_idx ON EMPLOYEE (LOWER(EMPLNAM))

Then the DBMS can use it when processing queries such as:

SELECT * FROM EMPLOYEE WHERE LOWER(EMPLNAM)="brown"

For general information about creating objects, see the chapter "Getting Started with PowerDesigner" in the General Features Guide .