Creating an Index

You can create the several 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.

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

For 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.

  1. Double-click a table symbol to display its property sheet and click the Indexes tab.
  2. Click the Add a Row tool and enter an index name and an index code.


  3. Click the Properties tool to open the property sheet of the new index.
  4. Type or select any appropriate index properties, and then click the Columns tab.
  5. To:
    • Create a user defined index, click the Add Columns tool, select one or more columns from the list, and then click OK
    • Create an index linked to a key, select the primary key, an alternate key, or foreign key from the Columns definition list
    • 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


  6. Select Ascending or Descending in the Sort column.
  7. 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||'%'

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 more information on the use of variables in reverse engineering queries, see Customizing and Extending PowerDesigner > DBMS Definition Files.

Parent topic: Indexes (PDM)