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:
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:
The following can be used to generate SQL to drop redundant indexes from the “stores_demo” database:
That produces:
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:
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:
Comments at the top of the function provide this shell script:
This should be run on the generated SQL to remove:
- carriage returns if unloaded using a Windows client;
- trailing backslashes and pipe symbols from UNLOAD;
- any spaces at end of line.
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:
Use that as follows:
The resulting CSV file can be loaded into Excel. In our example, that contains:
| Table | Index | Constraint | Leaves | Levels | Rows |
|---|---|---|---|---|---|
| cust_calls | 108_19 | r108_19 | 1 | 1 | 6 |
We are now ready to apply the changes:
- 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:
The full set of function parameters described in comments at the top are:
- Generate SQL to DROP indexes or UNDO the changes
- Database if different to where the function is defined
- Minimum number of rows for the table to be included (default 0)
- 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
- Examines only duplicate indexes
- Assumes B-tree indexes on normal data types
- Excludes fragmented indexes (beyond scope)
UNDO limitations
- CLUSTER not reapplied (would rewrite table)
- FILLFACTOR from $ONCONFIG (not in catalogs)
- 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.