Rebuilding IQ Indexes

As you develop a PDM or modify an existing one, you may change data types, alter the percentage of distinct values or change the number of values in tables. You must then rebuild the IQ indexes to reflect these changes.

Context

When you rebuild indexes, PowerDesigner determines the index type based on information contained from the table statistics, using 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 you have not specified a number of rows for the table, PD assumes that the table will include at least 1 row of data.

The rebuild process creates a FASTPROJECTION index for all columns, unless any of the following criteria apply:

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

Procedure

Select Tools Rebuild Objects Rebuild Indexes to open the Rebuild Indexes dialog box:
Select a default name to generates IQ indexes. You can use the following variables:
- %COLUMN% - Column name
- %INDEXTYPE% - Type of index to be rebuilt
- %TABLE% - Name or code of table (based on display preferences)
Specify a mode to use. You can choose between:
- Delete and Rebuild - All existing indexes are deleted before index rebuild
- Preserve Indexes - Preserves all existing indexes
[optional] Select the Include HNG and DATE/TIME indexes option to permit the creation of these specialized indexes for appropriate columns. If you do not select this option then only HG and LF indexes will be created.
[optional] Select the Update statistics before rebuild option to update such statistics as the number of records in a table and the number of distinct values in a column before performing the rebuild. Selecting this option can help with optimizing the rebuild.
[optional] Click the Selection tab and select or clear checkboxes to specify for which tables you want to rebuild indexes.
Click OK, and then Yes to confirm the rebuilding of your indexes.