SAP Sybase IQ: Index Advisor for a user

There are times when you need individual users the ability to determine what indexes are suggested by the index advisor but you don’t want or are unable to give them full dba access. All you need to do is grant the execute permission on the sp_iqindexadvice stored procedure.

Grant execute on sp_iqindexadvice to user;

create table test (col1 int, col2 varchar(10));
insert into test values (1, 'test1');
insert into test values (2, 'test2');
insert into test values (3, 'test3');
insert into test values (4, 'test4');
commit;

SET OPTION index_advisor = 'ON';
SET OPTION index_advisor_max_rows = 100;
commit;

select * from test where col1 =1 ;
commit;

call sp_iqindexadvice ();
Share Button

4 Replies to “SAP Sybase IQ: Index Advisor for a user”

  1. The advice entry buffer of 100 rows (index_advisor_max_rows = 100) should be sufficient for most queries but don’t be afraid to bump that up. Remember to turn the index_advisor off if you’re not actively using it.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.