Chapter 15 DBMS-Specific Features

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.

Steps To rebuild IQ indexes:

  1. Select Tools→Rebuild Objects→Rebuild Indexes to open the Rebuild Indexes dialog box:
  2. Select a default name to generates IQ indexes. There are three types of variables that you can use to define the default name:
    Variable Description
    %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:
    Mode Description
    Delete and Rebuild All existing indexes are deleted before index rebuild.
    Preserve Indexes Preserves all existing indexes.
  4. [optional] Select the Update statistics before rebuild checkbox 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.
  5. [optional] Click the Selection tab and select or clear checkboxes to specify for which tables you want to rebuild indexes.
  6. Click OK.

    A confirmation box asks if you want to continue.
  7. Click Yes.

    The IQ indexes are rebuilt.

Index types

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

Example in Adaptive Server IQ 12.5

Table A contains 1500 rows

Column % Distinct values Unique values
Col_1 integer 100 1500
Col_2 integer 50 750
Col_3 integer 0 0
Col_4 char (10) 100 1500
Col_5 char (10) 50 750

Rebuild indexes generates the following indexes:

Col_1 = HG index

Col_2 = LF index

Col_3 = no index

Col_4 = no index

Col_5 = LF index

 

Copyright (C) 2006. Sybase Inc. All rights reserved.