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

 

Comments

Post a Comment

Popular posts from this blog

SAP SYBASE IQ Partitioned Tables This section help find list of Partitioned tables in IQ   List of all the Partitioned tables in IQ SQL1> select b.table_name,b.table_type,a.create_time,a.update_time,z.dbspace_name from sysiqtable a, systable b, sysfile z where z.file_id=b.file_id and a.table_id=b.table_id and a.table_id in (select distinct table_id from syspartitions)   Details of particular table which is partitioned SQL2> select partition_id,partition_values,partition_name from syspartitions where table_id in (select table_id from systable where table_name=' <table_name> ')
Read more
SAP Sybase IQ Index Fragmentation This section show the fragmentation on indexes of a table in IQ: (SQL: sp_iqindexfragmentation ('table <owner.table>') )” , this Stored procedure can be used to check the index fragmentation. Stored procedure reports information about the percentage of page space taken up within the B-trees, garrays, and bitmap structures in Sybase IQ indexes.   Note: All percentages are truncated to the nearest percentage point. HG indexes also display the value of option GARRAY_FILL_FACTOR_PERCENT . Index types that use a B-tree also display the number of node (nonleaf) pages. These are HG , LF , WD , DATE , and DTTM .   For garrays, the fill percentage calculation does not take into account the reserved space within the garray groups, which is controlled by the GARRAY_FILL_FACTOR_PERCENT option. It specifies the percent of space on each HG garray pages to reserve for future incremental inserts into existing groups. The garray t...
Read more
How to find Invalid views on SAP Sybase IQ database This section shows list of invalid view in IQ (SQL: “select U.user_name , T.table_name as object_name from sysobject O, systab T, sysuser U where T.object_id=O.object_id and U.user_id=T.creator and O.status=2 ”). select U . user_name + '.' + T . table_name from sysobject O , systab T , sysuser U where T . object_id = O . object_id and U . user_id = T . creator and O . status = 2 /* Invalid */ and O . object_type = 2 /* views */   SYSOBJECT status TINYINT The status of the object. Values include: 1 (valid) - The object is available for use by the database server. This status is synonymous with ENABLED. That is, if you ENABLE an object, the status changes to VALID. 2 (invalid) - An attempt to recompile the object after an internal operation has failed, for example, after a schema-altering modification to an object on which it depen...
Read more