Chapter 14 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.

When you rebuild IQ indexes, two parameters are available:

Parameter Result of selection
Delete and Rebuild When selected all existing indexes are deleted before index rebuild.
Preserve Indexes When selected, preserves all existing indexes in a PDM.

Default name for 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)

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

Steps To rebuild IQ indexes:

  1. Select Tools→Rebuild Objects→Rebuild Indexes.

    The Rebuild Indexes dialog box opens to the General page.
  2. Select a default name to generates IQ indexes.

    The name may include column, table and index type definitions.
  3. Click the Selection tab.

    The Selection page appears.
  4. Select one or more tables from the list.
  5. Click OK.

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

    The IQ indexes are rebuilt.

 

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