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