Setting the level for B-tree scanner compression of indexes

Informix® provides several ways to specify the level at which B-tree scanner threads will compress indexes pages. To optimize space and transaction processing, you can lower the compression level if your indexes grow quickly. You can increase the level if your indexes have few delete and insert operations or if batch updates are performed.

Before you begin

Prerequisites:
  • Determine if adjusting the level for index compression will improve performance.
  • Get statistics on the number of rows read, deleted, and inserted by running the onstat -g ppf command. You can also view information in the sysptprof table.
  • Analyze the statistics to determine if you want to change the threshold.
For information about compression levels and the circumstances under which you might want to change the level, see B-tree scanner index compression levels and transaction processing performance.

Specify the compression level for an instance with any of the following options:

  • Set the compression field of the BTSCANNER configuration parameter to low, med (medium), high, or default. (The system default value is med.)
  • Run the onmode -C compression value command, where value is low, med (medium), high, and default. The system default value is med.
  • Run an SQL administration API function with this command: 
    SET INDEX COMPRESSION, partition number, compression level

Examples

Set the compression option of the BTSCANNER configuration parameter to default as follows: 

BTSCANNER num=4,threshold=10000,rangesize=-1,alice=6,compression=default

Set the compression option of the BTSCANNER configuration parameter to high as follows: 

BTSCANNER num=4,threshold=5000,compression=high

Specify the compression level using onmode -C, as follows: 

onmode –C compression high

Run either of the following SQL administration API functions to set the compression level for a single fragment of the index that has the partition number 1048960:

EXECUTE FUNCTION TASK("SET INDEX COMPRESSION", 1048960, "DEFAULT");
EXECUTE FUNCTION ADMIN("SET INDEX COMPRESSION", 1048960, "LOW");

Run the following SELECT statement to execute the task function over all index fragments. This command sets the compression level for all fragments of an index named idx1 in a database named db1.

SELECT sysadmin:TASK("SET INDEX COMPRESSION", partnum, "MED")
FROM sysmaster:systabnames
WHERE dbsname = 'dbs1' AND tabname = 'idx1';

You can also run the following SELECT TASK statement to execute the task function over all index fragments and set the compression level for all fragments.

SELECT TASK("SET INDEX COMPRESSION", partn, "MED")
FROM dbs1:systables t, dbs1:sysfragments f
WHERE f.tabid = t.tabid AND f.fragtype = 'I' AND indexname ='idx1';