sp_iqcardinality_analysis Procedure

Helps you generate indexes by analyzing the cardinality of the columns in the table and recommending the indexes to add.

sp_iqcardinality_analysis can generate an optional SQL script containing ready-to-execute SQL statements for index creation.

sp_iqcardinality_analysis operates independently of the Index Advisor. The Index Advisor gives recommendations based on the usage of a column in actual queries, but only considers cardinality in the case where the column has an LF or HG index, and distinct count allows rebuilding 1-byte FP and 2-byte FP indexes. sp_iqcardinality_analysis considers cardinality in all cases, but does not include the Index Advisor recommendations in its index recommendation list.

sp_iqcardinality_analysis does not include HNG and CMP indexes in its list of recommendations. Recommended indexes include:

  • LF index

  • HG index

  • 1-byte FP

  • 2-byte FP

  • 3-byte FP

  • Conversion of 2-byte FP to 1-byte FP

  • Primary Key Constraint (HG index)

  • Unique Constraint (HG index)

  • DATE/TIME/DTTM index

  • WD index

  • WORD index

Note: Executing sp_iqcardinality_analysis against a wide table may take several minutes, depending on the complexity of the column data types and indexes already existing on the table.

Syntax

sp_iqcardinality_analysis ( [ ‘table_name’ ], [ ‘table_owner’ ], [ ‘script’ ] )

Parameters

Parameter

Description

table_name

Name of the table.

table_owner

Name of the table owner. If this parameter is not specified, then the procedure looks for a table owned by the current user.

script

Generates a SQL script containing ready-to-execute SQL statements that create the indexes, and displays index recommendations. If this option is not specified, then the console displays:

  • table_name

  • table_owner

  • column_name

  • cardinality

  • index_type

  • index recommendation

Usage

If you do not specify any parameters, then IQ displays create_index SQL statements for all columns in all tables owned by the current user.

If you specify script, you can redirect the output to generate the script file:

OUTPUT TO ‘indexfile.sql’ FORMAT ASCII QUOTE '';

Permissions

Requires DBA authority.

Example 1

sp_iqcardinality_analysis 'onebytefp', 'DBA'
Example 1 console output

table_name

table_owner

column_name

cardinality

index type

index recommendation

onebytefp

DBA

c1

10

One Byte FP

--Column 'c1' has no One Byte FP index and cardinality is less than 256. --One Byte FP index can be created.--Call the stored procedure: sp_iqrebuildindex 'onebytefp','column c1 255'

onebytefp

DBA

c1

10

LF

--Column 'c1' has no LF index and cardinality is less than 1000. --LF index can be created using CREATE INDEX statement: CREATE LF INEDX IQ_T400_c1_LF ON DBA.onebytefp (c1)

Example 2

sp_iqcardinality_analysis 'onebytefp', 'DBA', 'script'
Example 2 console output

index recommendation

--Column 'c1' has no One Byte FP index and cardinality is less than 256. --One Byte FP index can be created. --Call the stored procedure: sp_iqrebuildindex 'onebytefp','column c1 255' --Column 'c1' has no LF index and cardinality is less than 1000. --LF index can be created using CREATE INDEX statement: CREATE LF INDEX IQ_T400_c1_LF ON onebytefp (c1)