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.
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):
The standard index has 2783 used pages, 2766 leaves, and 3 levels.
SQL to replace this with a partial index excluding invoiced rows is:
- Stating the dbspace name before “INDEX OFF” is recommended as it’s necessary for mixed page sizes: see Caveats.
- Partition names can be whatever you like instead of “part_0” and “part_1” within the normal Informix object name rules.
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:
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:
Then run the new function:
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:
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:
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.
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”.
This article was updated on 18th August 2020 with new information from Roland Wintgen who has opened a case with IBM. A problem occurs if the database or table is in a dbspace with a non-default page size. For example, in an instance with 2KB pages by default, if you create a 4KB page dbspace “data4kb” then a new database in it, the following simpler form of SQL for one of our examples produces an error:
You have to declare a dbspace name for both fragments in this situation. Perhaps it otherwise defaults to the root dbspace, causing the error. However, it still fails:
The solution is also to name the fragments (aka. partitions):
The stored procedure and examples in this article have been amended accordingly. If accepted as a bug by IBM, we will provide further updates here with the defect number and IDS version containing the fix when either are available. Meanwhile, the above is a full work-around.
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.
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.
If you have any questions regarding Informix 14.10 Partial Indexes or would like to find out more, simply contact us.