Indexes (PDM)

An index is a data structure associated with one or more columns ordered by the column values and used to improve read access times. You normally create indexes for columns that you search on regularly, and where response time is important. Most types of index are more effective when applied to columns with high cardinality.

You can create the following types of index:

Standard index - Associated with one or more columns containing high-cardinality values that are frequently searched on.
Key index - Associated with a primary, foreign, or alternate key. Indexes linked to keys are unique because they are based on the same columns as the key, and are conventionally named after the table with a _PK, _FK, or AK suffix (for example, Project_AK).
Function-based index - [if supported by the DBMS] Associated with a function or expression, which calculates values based on one or more columns to populate the index. Function-based indexes provide an efficient mechanism for evaluating statements that contain functions in their WHERE clauses.

For example, in an Author table, you might create an index for the primary key Author ID and another for the Author name column, as it is regularly searched on. You will not create an index for the Author Home City column, as it is not often searched on.

Reverse Engineering Function-based Index

Index columns with expressions have 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 Customizing and Extending PowerDesigner > DBMS Definition Files.

Creating an Index
Index Properties
To view or edit an index'sproperties, double-click its Browser or list entry. The property sheet tabs and fields listed here are those available by default, before any customization of the interface by you or an administrator.
Rebuilding Indexes
Rebuilding indexes in a physical diagram automatically updates any changes that you have made to primary keys, foreign keys, or alternate keys in your model.
Indexes in Query Tables
You can create an index associated with the columns of a query table, which is a special type of view available in Oracle and DB2. These indexes are called view indexes. Query table indexes behave like indexes defined on tables, they are data structures that improve database performance and access speed. You normally create indexes for columns that you access regularly, and where response time is important.

Parent topic: Physical Diagrams