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)