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 tries to pad out each group to include a pad of empty space set by the value. This space is used for rows added to existing index groups. Default value is 25.

 
GARRAY_PAGE_SPLIT_PAD_PERCENT option, determines per-page fill factor during page splits on the garray and specifies the percent of space on each HG garray page to reserve for future incremental inserts. Splits of a garray page try to leave that percentage empty. This space is used for rows added to new index groups. Default value: 25.

 
sp_iqrowdensity ('table <owner>. <table_name>') //This stored procedure can be used to report row fragmentation

 

Additionally,

sp_iqindex ('<table_name>')0

sp_iqcolumn ('<table_name>')

sp_iqindexfragmentation ('table <owner>. <table_name>')

sp_iqindexsize ('<owner>. <table_name>.<index_name>')

sp_iqindexinfo ('table <owner>. <table_name>')

sp_iqspaceinfo('table <owner>. <table_name>')

sp_iqcheckdb('verify index <owner>. <table_name>.<index_name>')

CALL sa_index_density( 'Customers' );

To rebuild index if issue found:

-         Store procedure sp_iqrebuiltindex can be used to rebuild the indexes and reduce the fragmentation.

e.g.

        sp_iqrebuiltindex (<table_name>, <index_clause>)

Comments

Post a Comment

Popular posts from this blog

Enable and Disable Trace in SAP Sybase IQ database Enable Trace in IQ DB   1.   Check the table meta info in dbisql:     sp_iqobjectinfo [owner],[table_name];   2.   Enable SQL or Query Plan generator – On the load target IQ server – run these commands with DBA: create a temp folder to save trace and logs:     mkdir /tmp/log_iqload   SQL Trace: in dbisql:     call sa_server_option('request_level_logging', 'SQL');     call sa_server_option('request_level_log_file','/tmp/log_iqload/sqltrace.log');   Query plan: in dbisql:     set option query_plan='on';     set option query_detail='on';     set option query_plan_as_html = 'on';     set option dml_options10='on';     set option query_plan_after_run='on';     set option query_name ='load_plan';     set option query_plan_text_caching='on'     set option query_plan_as_html_dire
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
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.
Read more