When you rebuild indexes, PowerDesigner determines the index type based on information contained from the table statistics. It uses the number field, which indicates the estimated number of records per table, and the percentage of distinct values to compute the number of unique values. If the user has not specified a number of rows for the table, PD assumes that the table will include at least 1 row of data.
Usually the rebuild process creates a FASTPROJECTION index for all columns. Otherwise, the following rules are applied:
|
Criteria |
Index type |
|---|---|
|
If no statistics are provided and the column has an undefined data type |
No index is created |
|
Low number of unique values in a column Column used in join predicate |
LOWFAST |
|
High number of unique values in a column No COUNT DISTINCT, SELECT DISTINCT, or GROUP BY queries required |
HIGHNONGROUP |
|
Column used in join predicate High number of unique values in a column (more that 1000) Anticipate COUNT DISTINCT, SELECT DISTINCT, or GROUP BY queries Column must enforce uniqueness |
HIGHGROUP |
|
Column without numeric datatype |
No index is created |
|
Column with date type |
DATE |
|
Column with time type |
TIME |
|
Column with datetime or smalldatetime type |
DTTM |
For example (IQ v12.5, Table A contains 1500 rows