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

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

• table_name

• table_owner

• column_name

• cardinality

• index_type

• index recommendation

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 '';

Requires DBA authority.

sp_iqcardinality_analysis 'onebytefp', 'DBA'

sp_iqcardinality_analysis 'onebytefp', 'DBA', 'script'