Abstract

Informix indexes are allowed but usually not needed if the indexed columns are already at the front of another index, as the other will be used instead. This article provides a Stored Procedure Language (SPL) function to report such redundant indexes and generate SQL to drop them. Space will be released and index update overhead reduced.

There might be uncommon scenarios on some systems in which a whole index is frequently scanned and is quicker with less columns, so the generated SQL should be reviewed and applications retested before deploying in production.

Content

Examples in this article were produced with the provided stores_demo database in IDS 14.10.FC14 Developer Edition from Fix Central installed in a Rocky 9 Docker container as described here.

The “cust_calls” table has an example of such a redundant index. It has these referential constraints:

Copy to Clipboard

Unless explicitly created in a separate statement beforehand, indexes on the column involved will automatically be created for a referential constraint. The index name will begin with a space to denote that it should be hidden by tools such as dbschema.

The second statement above gives rise to an index on “customer_num” which is unnecessary as the primary key index begins with that column. The fix is to have “INDEX DISABLED” appended to that statement:

ALTER TABLE cust_calls ADD CONSTRAINT PRIMARY KEY (customer_num, call_dtime);
ALTER TABLE cust_calls ADD CONSTRAINT FOREIGN KEY (customer_num) REFERENCES customer INDEX DISABLED;
ALTER TABLE cust_calls ADD CONSTRAINT FOREIGN KEY (call_code)    REFERENCES call_type;


This is in fact the classic case where redundant indexes arise, namely foreign keys at the beginning of a primary key.

A solution to identifying and fixing all such occurrences is to install the following SPL functions in an administration database:

Copy to Clipboard
Copy to Clipboard


The following can be used to generate SQL to drop redundant indexes from the “stores_demo” database:

Copy to Clipboard


That produces:

Copy to Clipboard


There is no alternative to dropping and recreating the constraint with INDEX DISABLED appended. However, the first line above means that the contents are not checked against the primary table when the constraint is added back, so this should be quick. The tables involved will be exclusively locked, so any such change on a production system should be in a maintenance window with the instance in administration mode.

The second line is for information and contains the table, hidden index and constraint names, followed by useful metrics to see how big the index is.

Before applying the change, you should also generate the code to undo the change should testing fail:

Copy to Clipboard


In this case, it produces the same SQL but without INDEX DISABLED. In other cases with explicit indexes, this is vital as necessary information required on the original state will have been lost.

In real cases, it might generate SQL to drop hundreds of indexes, so it will be more convenient to write the results directly to a file:

Copy to Clipboard


Comments at the top of the function provide this shell script:

Copy to Clipboard


This should be run on the generated SQL to remove:

  1. carriage returns if unloaded using a Windows client;
  2. trailing backslashes and pipe symbols from UNLOAD;
  3. any spaces at end of line.
Copy to Clipboard


It is useful also to produce a report with a summary of the indexes being dropped, so comments at the top of the function provide this “awk” script:

Copy to Clipboard


Use that as follows:

Copy to Clipboard


The resulting CSV file can be loaded into Excel. In our example, that contains:

TableIndexConstraintLeavesLevelsRows
cust_calls108_19r108_19116

We are now ready to apply the changes:

Copy to Clipboard
  • Use “nohup … &” to safely run in background as this might take a while on a real system.
  • “dbaccess -a” will abort if any error is encountered (same as “export DBACCNOIGN=1”).
  • “dbaccess -e” echoes statements so you can watch progress and know where it was if it fails.
  • You can monitor during the run with “tail -f drop.out”.

Should you need to reinstate the indexes:

Copy to Clipboard


The full set of function parameters described in comments at the top are:

  1. Generate SQL to DROP indexes or UNDO the changes
  2. Database if different to where the function is defined
  3. Minimum number of rows for the table to be included (default 0)
  4. Skip indexes supporting foreign key constraints (default off)

If you wished to reduce the number of statements in the change, you could generate only the most important ones by limiting tables only to those with more than a certain number of rows.

If you were nervous about the affect on nested loop joins – which might be a concern with older versions – you can specify ‘t’ as the last parameter.

Caveats

From the comments at the top of the function:

DROP limitations

  1. Examines only duplicate indexes
  2. Assumes B-tree indexes on normal data types
  3. Excludes fragmented indexes (beyond scope)

UNDO limitations

  1. CLUSTER not reapplied (would rewrite table)
  2. FILLFACTOR from $ONCONFIG (not in catalogs)
  3. COMPRESSED/COLLATION ignored (beyond scope)

Conclusion

If you have large redundant indexes in your databases, following this article will save disk space and make inserts and deletes lighter, but test in a non-production environment first just in case more expensive query plans result.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Contact Us

If you have any questions or would like to find out more about this topic, please contact us.

Author