Chapter 15 DBMS-Specific Features
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
| Column | % Distinct
values |
Unique
values |
Rebuild indexes generates |
|---|---|---|---|
| Col_1 integer | 100 | 1500 | HG index |
| Col_2 integer | 50 | 750 | LF index |
| Col_3 integer | 0 | 0 | no index |
| Col_4 char (10) | 100 | 1500 | no index |
| Col_5 char (10) | 50 | 750 | LF index |
| Copyright (C) 2007. Sybase Inc. All rights reserved. |
| |