Abstract

Best practice has always been not to create indexes on highly duplicate data. Scanning the entire table for a common value may be quicker, and updating an index can be very costly when many pointers to rows with the same value are spread over several pages. A work-around when an index is essential in this scenario is to extend the index with a more selective column, but this obviously makes it bigger.

For the first time, a far better solution was provided in IDS 14.10.FC2. It’s known as Informix 14.10 Partial Indexes

Partial Indexes (click for the relevant page in the IBM Knowledge Center).

In this article, we will demonstrate how to identify where such indexes might be appropriate, how to create them, and how much smaller they can potentially be.

Content

A classic example is the index on the “status” column of the Sage Line 500 “opheadm” table which contains sales orders. 99% of rows will typically have the value “8” for “Invoiced” in the status column: if we wanted to retrieve all those rows, the query optimizer will probably find it quicker to scan the whole table without using any index. However, most of the time, we are looking for rows with one of the other values, for which we do need an index. The following simulates the scenario (the real table obviously has many more columns):

Copy to Clipboard

That returns:

statusnumber
11000
21000
31000
41000
51000
61000
71000
8993000

The standard index has 2783 used pages, 2766 leaves, and 3 levels.

SQL to replace this with a partial index excluding invoiced rows is:

Copy to Clipboard

The new index has 24 used pages, 22 leaves, and 2 levels.

The following function helps you identify indexes that could be candidates, and generates SQL to replace them with partial indexes:

Copy to Clipboard

It checks all duplicate indexes on single columns in a specified database to see if there are values accounting for more than a given percentage of rows in the table. For example, create and populate the example table as described in the IBM Knowledge Center Partial Indexes page, but with this index:

Copy to Clipboard

Then run the new function:

Copy to Clipboard

SQL generated:

Copy to Clipboard

The number of leaves and levels is shown so that you can decide which indexes are big enough to matter.

Run on the standard demo database:

Copy to Clipboard

SQL generated:

Copy to Clipboard

The extra first statement above will be some help in dealing with indexes implied by primary or foreign key constraints, whose actual name begins with a space to prevent alteration: see the RENAME INDEX documentation page.

However, CREATE INDEX still fails with error -350 “Index already exists on the column” as the index has not in fact been dropped but only hidden again. The following is a complete solution using more meaningful object names:

Copy to Clipboard

As you can see, the SQL generated is only a guide, and you may well need to edit the results, as well as experimenting with different thresholds.

Caveats

Tables comprised of multiple fragments (partitions) is part of the parallelisation features reserved for Enterprise Edition. Unfortunately, Informix 14.10 Partial Indexes have been implemented using the same FRAGMENT BY EXPRESSION (or PARTITION) syntax, and this is rejected on lower editions (except Developer) with error 26453 “Fragmentation is not supported in this edition of IDS”.

Conclusion

If Informix 14.10 Partial Indexes are available in your version and edition, they may save you considerable disk space and make reads, inserts and deletes lighter.

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 regarding Informix 14.10 Partial Indexes or  would like to find out more, simply contact us.

Contact us