Informix 14.10 Partial Indexes can save disk space

Abstract

Updated July 2024

Thanks to Andreas Ledger, RFE INFX-I-368 has been delivered, and this feature is now available in all IDS Editions from version 14.10.FC11 onwards.

Updated February 2026

The procedure provided to generate implementation SQL now fully handles foreign key constraints and has new parameters.

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, known as 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:

status	number
1	1000
2	1000
3	1000
4	1000
5	1000
6	1000
7	1000
8	993000

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

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:

sp_partial_indexes.sql

Copy to Clipboard

DROP FUNCTION IF EXISTS sp_partial_indexes;

CREATE FUNCTION sp_partial_indexes
    (
        p_database  VARCHAR(128) DEFAULT NULL,
        p_percent   SMALLFLOAT   DEFAULT 50,
        p_nrows     INT          DEFAULT 1000,
        p_conskip   BOOLEAN      DEFAULT 'f'
    )
    RETURNING LVARCHAR AS sql;

/*
    Generate SQL to recreate recommended partial indexes
    Doug Lawry, Oninit Consulting

Version 1: Apr 2020
    Version 2: Feb 2026

References:
    www.oninitgroup.com/faq-items/informix-14-10-partial-indexes

Parameters:
    1) Database if different to where the function is defined
    2) Percentage of rows for a column value to be a candidate
    3) Minimum number of rows for the table to be included
    4) Skip indexes supporting foreign key constraints
    5) Generate statements to undo partial indexes

Limitations:
    1) Only examines single-column duplicate indexes
    2) Ignores indexes that are disabled or already fragmented

Example using all parameters:
    EXECUTE FUNCTION sp_partial_indexes('stores_demo',40,5,'f');

Direct file output:
    UNLOAD TO 'filename'
    SELECT * FROM TABLE(FUNCTION(sp_partial_indexes()));

Shell script to fix unloads:
    for i
    do
        sed -i \
            -e 's/\r//' \
            -e 's/\\$//' \
            -e 's/|/\n/' \
        "$i"
    done

Shell script to create rollback SQL:
    for i
    do
        sed \
            -e 's/^ON .*$/&;/' \
            -e 's/ ;/;/' \
            -e '/^PARTITION /d' \
            -e '/^FRAGMENT /d' \
        "$i" > rollback.$i
    done
*/

DEFINE  l_sql       LVARCHAR;

DEFINE  l_idxowner,
            l_idxname,
            l_tabname,
            l_colname,
            l_constrowner,
            l_constrname,
            l_ptabname,
            l_pcolname,
            l_dbspace,
            l_value,
            l_metrics   VARCHAR(128);

DEFINE  l_constrid,
            l_nrows,
            l_leaves,
            l_levels,
            l_minimum,
            l_fragments,
            l_count,
            l_percent   INT8;

DEFINE  l_state,
            l_delrule   CHAR(1);

SET DEBUG FILE TO '/tmp/sp_partial_indexes.debug';
    TRACE ON;

IF p_database IS NULL THEN

SELECT TRIM(odb_dbname) -- main session database
        INTO p_database
        FROM sysmaster:sysopendb
        WHERE odb_sessionid = DBINFO('sessionid')
        AND odb_odbno = 0;

END IF

LET l_sql =
        ' SELECT ' || 'TRIM(I.owner),' ||
        ' '        || 'I.idxname,' ||
        ' '        || 'T.tabname,' ||
        ' '        || 'C.colname,' ||
        ' '        || 'F.constrid,' ||
        ' '        || 'T.nrows,' ||
        ' '        || 'I.leaves,' ||
        ' '        || 'I.levels' ||
        ' FROM '   || p_database || ':sysindexes AS I' ||
        ' JOIN '   || p_database || ':systables  AS T' ||
        ' ON '     || 'T.tabid = I.tabid' ||
        ' JOIN '   || p_database || ':syscolumns AS C' ||
        ' ON '     || 'C.tabid = I.tabid' ||
        ' AND '    || 'C.colno = I.part1' ||
        ' JOIN '   || p_database || ':sysobjstate AS O' ||
        ' ON '     || 'O.tabid = I.tabid' ||
        ' AND '    || 'O.name = I.idxname' ||
        ' AND '    || 'O.objtype = ''I''' ||
        ' AND '    || 'O.state = ''E''' || ' LEFT OUTER' ||
        ' JOIN '   || p_database || ':sysconstraints AS F' ||
        ' ON '     || 'F.tabid = I.tabid' ||
        ' AND '    || 'F.idxname = I.idxname' ||
        ' WHERE '  || 'I.idxtype = ''D''' ||
        ' AND '    || 'I.part2 = 0' ||
        ' AND '    || 'T.nrows >= ' || p_nrows;

PREPARE e_main FROM l_sql;
    DECLARE c_main CURSOR FOR e_main;
    OPEN    c_main;

LET l_metrics = NULL;

WHILE 1 = 1

LET l_idxname = NULL;

FETCH c_main INTO
            l_idxowner,
            l_idxname,
            l_tabname,
            l_colname,
            l_constrid,
            l_nrows,
            l_leaves,
            l_levels;

IF l_idxname IS NULL THEN
            EXIT WHILE;
        END IF

IF l_constrid IS NOT NULL AND p_conskip THEN
            CONTINUE WHILE;
        END IF

SELECT  COUNT(*)
        INTO    l_fragments
        FROM    sysmaster:systabnames
        WHERE   dbsname = p_database
        AND     tabname = l_idxname;

IF l_fragments > 1 THEN
            CONTINUE WHILE; -- may already be a partial index
        END IF

LET l_minimum = l_nrows * p_percent / 100;

LET l_sql =
            'SELECT ' || l_colname  || ', COUNT(*)' ||
            ' FROM '  || p_database || ':' || l_tabname ||
            ' GROUP BY 1 ' ||
            ' HAVING COUNT(*) > '  || l_minimum ||
            ' ORDER BY 2 DESC, 1';

PREPARE e_values FROM l_sql;
        DECLARE c_values CURSOR FOR e_values;
        OPEN    c_values;

LET l_sql = NULL;

WHILE 1 = 1

LET l_count = null;

FETCH c_values INTO
                l_value,
                l_count;

IF l_count IS NULL THEN
                EXIT WHILE;
            END IF

IF LENGTH(l_value) > 20 THEN
                -- Probably transient
                CONTINUE WHILE;
            END IF

IF l_sql IS NULL THEN

SELECT  MIN(TRIM(S.name))
                INTO    l_dbspace
                FROM    sysmaster:systabnames AS T,
                        sysmaster:sysdbspaces AS S
                WHERE   T.dbsname = p_database
                AND     T.tabname = l_idxname
                AND     S.dbsnum  = T.dbsnum;

IF l_metrics IS NULL THEN

-- Pointless to recheck foreign keys, far quicker without
                    -- Statement deferred for empty output if no data found

RETURN 'SET ENVIRONMENT NOVALIDATE ON;' WITH RESUME;

END IF

LET l_percent = 100 * l_count / l_nrows;

LET l_metrics =
                    ' {'  || l_leaves  || ' leaves,' ||
                    ' '   || l_levels  || ' levels,' ||
                    ' '   || l_nrows   || ' rows,'   ||
                    ' '   || l_percent || '%}';

IF l_constrid IS NOT NULL THEN

LET l_sql =
                        ' SELECT ' || 'TRIM(F.owner),' ||
                        ' '        || 'F.constrname,' ||
                        ' '        || 'O.state,' ||
                        ' '        || 'R.delrule,' ||
                        ' '        || 'T.tabname,' ||
                        ' '        || 'C.colname' ||
                        ' FROM '   || p_database || ':sysconstraints AS F' ||
                        ' JOIN '   || p_database || ':sysobjstate AS O' ||
                        ' ON '     || 'O.name = F.constrname' ||
                        ' AND '    || 'O.owner = F.owner' ||
                        ' AND '    || 'O.objtype = ''C''' ||
                        ' JOIN '   || p_database || ':sysreferences AS R' ||
                        ' ON '     || 'R.constrid = F.constrid' ||
                        ' JOIN '   || p_database || ':sysconstraints AS P' ||
                        ' ON '     || 'P.constrid = R.primary' ||
                        ' JOIN '   || p_database || ':systables AS T' ||
                        ' ON '     || 'T.tabid = R.ptabid' ||
                        ' JOIN '   || p_database || ':sysindexes AS I' ||
                        ' ON '     || 'I.tabid = R.ptabid' ||
                        ' AND '    || 'I.idxname = P.idxname' ||
                        ' JOIN '   || p_database || ':syscolumns AS C' ||
                        ' ON '     || 'C.tabid = I.tabid' ||
                        ' AND '    || 'C.colno = I.part1' ||
                        ' WHERE '  || 'F.constrid = ' || l_constrid;

PREPARE e_constraint FROM l_sql;
                    DECLARE c_constraint CURSOR FOR e_constraint;
                    OPEN    c_constraint;

FETCH   c_constraint INTO
                        l_constrowner,
                        l_constrname,
                        l_state,
                        l_delrule,
                        l_ptabname,
                        l_pcolname;

CLOSE   c_constraint;
                    FREE    c_constraint;
                    FREE    e_constraint;

LET l_sql =
                        'ALTER TABLE ' || l_tabname ||
                        ' DROP CONSTRAINT ' || l_constrname ||
                        l_metrics || ';';

LET l_metrics = '';

RETURN l_sql WITH RESUME;

END IF

IF l_idxname MATCHES ' *' THEN

-- Hidden index already dropped with constraint
                    -- Must rename to recreate as an explicit index

LET l_idxname = 'ix_' || TRIM(l_idxname);

ELSE

LET l_sql =
                        'DROP INDEX ' || l_idxname ||
                        l_metrics || ';';

RETURN l_sql WITH RESUME;

END IF

LET l_sql =
                    'CREATE INDEX' ||
                    ' ''' || l_idxowner ||
                    '''.' || l_idxname  || ' '  || CHR(10) ||
                    'ON ' || l_tabname  ||
                    ' ('  || l_colname  || ') ' || CHR(10) ||
                    'FRAGMENT BY EXPRESSION '   || CHR(10) ||
                    'PARTITION part_0 (';

END IF

-- Append column values to be excluded from the index

IF l_value IS NULL THEN

LET l_sql = l_sql || l_colname || ' IS NULL';

ELSE

IF l_sql MATCHES '* IS NULL' THEN
                    LET l_sql = l_sql || ' OR ';
                END IF

IF l_sql MATCHES '*''' THEN
                    LET l_sql = l_sql || ',';
                ELSE
                    LET l_sql = l_sql || l_colname || ' IN (';
                END IF

LET l_sql = l_sql || '''' || l_value || '''';

END IF

END WHILE

CLOSE   c_values;
        FREE    c_values;
        FREE    e_values;

IF l_sql IS NULL THEN
            CONTINUE WHILE;
        END IF

IF l_sql MATCHES '*''' THEN
            LET l_sql = l_sql || ')';
        END IF

LET l_sql = l_sql ||
            ') IN ' || l_dbspace || ' INDEX OFF, ' || CHR(10) ||
            'PARTITION part_1 REMAINDER' ||
            ' IN ' || l_dbspace || ';';

RETURN l_sql WITH RESUME;

IF l_constrid IS NOT NULL THEN

LET l_sql =
                'ALTER TABLE '     || l_tabname     ||
                ' ADD CONSTRAINT ' ||                           CHR(10) ||
                'FOREIGN KEY ('    || l_colname     || ') '  || CHR(10) ||
                'REFERENCES '      || l_ptabname    || ' ('  ||
                                      l_pcolname    || ') '  || CHR(10) ||
                CASE l_delrule
                    WHEN 'C'
                        THEN 'ON DELETE CASCADE '            || CHR(10)
                        ELSE ''
                END ||
                'CONSTRAINT '''    || l_constrowner || '''.' ||
                                      l_constrname  ||
                CASE l_state
                    WHEN 'D'
                        THEN ' DISABLED'
                        ELSE ''
                END || ';';

RETURN l_sql WITH RESUME;

END IF

END WHILE

CLOSE   c_main;
    FREE    c_main;
    FREE    e_main;

END FUNCTION;

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, levels and rows is shown so that you can decide which indexes are big enough to matter, plus the largest percentage of rows with a column value to be excluded from the index

Run on the standard demo database:

Copy to Clipboard

SQL generated:

Copy to Clipboard

Additional statements are required for indexes supporting foreign keys. The constraint must be dropped first, without which we cannot rebuild the index. There are two scenarios regarding whether we also need to drop the index explicitly:

If the index was previously created automatically by an ADD CONSTRAINT statement, it is hidden and has a name beginning with a space followed by the table ID and a sequential number. It will be dropped with the constraint. In order to recreate it separately before the constraint is added back, it must have a valid name that does not begin with a space, so the procedure replaces the leading space with “ix_”.
If the index was previously created explicitly before an ADD CONSTRAINT statement, it must be dropped explicitly after the constraint.

The procedure handles all of that. You will also find that the generated SQL always begins with:

Copy to Clipboard

That makes it way quicker to recreate constraints, as it doesn’t then pointlessly recheck column values in the referenced table: they must have been there just before when the constraint already existed.

The full set of parameters are:

Database if different to where the function is defined
Percentage of rows for a column value to be a candidate (default 50%)
Minimum number of rows for the table to be included (default 1000)
Skip indexes supporting foreign key constraints (default off)

You may want to experiment with different thresholds in 2 and 3. We recommend using the defaults as a starting point, but you can change those in the code as is suitable for your system:

50% means you will only ever have one column value listed, which keeps it simple, and is a good compromise between saving space but still indexing values that might have a purpose.
Less than 1000 rows is the normal definition of a “small table” for Automated Update Statistics and the Query Optimizer. The index will quite likely not be used and its size will be trivial, so we might as well exclude it from the generated SQL to keep it shorter.

Caveats

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

This article was updated in August 2020 with new information from Roland Wintgen who 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:

Copy to Clipboard

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:

Copy to Clipboard

The solution is also to name the fragments (aka. partitions):

Copy to Clipboard

The stored procedure and examples in this article were 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 known. Meanwhile, the above is a full work-around.

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.