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.

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

  1. Select Tools > Rebuild Objects > Rebuild Indexes to open the Rebuild Indexes dialog box:


  2. 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)

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

  4. [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.
  5. [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.
  6. [optional] Click the Selection tab and select or clear checkboxes to specify for which tables you want to rebuild indexes.
  7. Click OK, and then Yes to confirm the rebuilding of your indexes.