SAP SYBASE IQ Index Advisor

sp_iqindexadvice Procedure

Displays stored index advice messages. Optionally clears advice storage.

SQL> select * from sa_conn_options() where OptionName like '%Index_Adv%' and number=connection_property ('Number')

Enable Index Advisor in IQ DB

 

Option1:

1.      SET OPTION index_advisor = 'ON';
2.      SET OPTION index_advisor_max_rows = 100;
3.      commit;
4.      <query to be analyzed>;
5.      call sp_iqindexadvice ();

 

Option2:

1.      set temporary option INDEX_ADVISOR = on;
2.      set temporary option INDEX_ADVISOR_MAX_ROWS = 20;
3.      commit;
4.      -- sql statements
5.      call sp_iqindexadvice ();

The above code does two things for you-

1. It tells you that your option are set correctly.

2. If table structure is not optimal relative to query, the advisor should give some advice.
 

Syntax

sp_iqindexadvice ( [ resetflag ] )

Usage

Parameter	Description
resetflag	Lets the caller clear the index advice storage. If resetflag is nonzero, all advice is removed after the last row has been retrieved.

Description

Allows users to query aggregated index advisor messages using SQL. Information can be used to help decide which indexes or schema changes will affect the most queries.

 

INDEX_ADVISOR columns:

sp_iqindexadvice columns
Column name	Description
Advice	Unique advice message
NInst	Number of instances of message
LastDT	Last date/time advice was generated

 

Example

Sample output from the sp_iqindexadvice procedure:

Advice	NInst	LastDT
Add a CMP index on DBA.tb (c2, c3) Predicate: (tb.c2 = tb.c3)	2073	2009-04-07 16:37:31.000
Convert HG index on DBA.tb.c4 to a unique HG	812	2009-04-06 10:01:15.000
Join Key Columns DBA.ta.c1 and DBA.tb.c1 have mismatched data types	911	2009-02-25 20:59:01.000

 

select index_type, index_name from sp_iqindex('OWNER.TABLENAME');

select * from sp_iqindexmetadata(‘<INDEX_NAME>’, 'OWNER.TABLENAME');

sp_iqindexadvice

sp_iqindexuse