How to run update statistics manually for informix table which is having composite indexes?

Suppose there is a table with the schema shown below containing a large number of records. For better performance, I execute update statistics manually. I need to know what is the correct way to execute the update statistics command for composite indexes. How should I use medium and high depending on the situation?

create table "informix".table1
  (
    ip_address char(15),
    mobile_no char(10),
    name char(20)
  ) in dbs1 extent size 64 next size 32 lock mode row;



create unique index "informix".idx_table1_1 on "informix".table1
    (ip_address,name) using btree  in idxdbs;

For the table above, which is the best way to update statistics from the two methods below?

update statistics high for table table1(ip_address,name) force;

update statistics medium for table table1(name) force;
update statistics high for table table1(ip_address) force;

Solution

The Informix 12.10 manual suggests:

Using the MEDIUM mode option

After UPDATE STATISTICS MEDIUM has been run on a table, the query optimizer typically chooses a more efficient execution plan, compared to the same SELECT statement when only LOW mode column distribution statistics are available for the table. column.
Using the HIGH mode option:

For indexed tables that already have MEDIUM mode distribution statistics available for every column, the query optimizer typically chooses more efficient execution plans after you run UPDATE STATISTICS HIGH on every column that is part of an index key.

Since both ip_address and name are part of the unique index (you should consider designating it as the primary key too), the first option — the single HIGH mode statement — is probably most appropriate. There's no need to run the MEDIUM mode on the same columns first.

Test it — YMMV.