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
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:
|
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 '';
sp_iqcardinality_analysis 'onebytefp', 'DBA'
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) |
sp_iqcardinality_analysis 'onebytefp', 'DBA', 'script'
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) |