Starting with version Informix 14.10xC11 (currently 14.10xC12), it is possible in Informix to use partial indexes in conjunction with FRAGMENT BY EXPRESSION specifically for memory reduction and performance optimization because table rows are queried only partly.

The basic idea behind it: a partial index only indexes rows that meet a WHERE condition. Consequently, not every row ends up in the index, and the index itself is smaller, faster, and easier to maintain. Only relevant rows are indexed and selectively distributed across multiple (specific) database spaces using FRAGMENT BY EXPRESSION. As a result, this approach leads to less I/O and better cache utilization in everyday operation, since the index remains in the buffer

Typical examples are:

• Separating historical and active data: Queries on active data are typically much more frequent in production systems than vice versa. "Active" data could also be stored on fast storage, for example, if different storage classes are used.
• Time-dependent queries: This would be particularly useful for large tables and for monitoring and reporting. By indexing only current data (e.g., orders from a specific day), the history is ignored, leading to performance improvements
• Status- or flag-based queries: For example, an overview of open orders is conceivable

The following has to be taken in consideration:

• The query matches the WHERE condition
• The WHERE condition is stable
• The optimizer is a good fit > i. e. regular execution of Update Statistics

As a result, partial indexes are ideally suited to selectively indexing and efficiently distributing comparatively small but active datasets while simultaneously avoiding the overhead of "inactive" data, as this can be "excluded" due to the targeted indexing

Benefits of partial indexes are:

• Less I/O
• Improved Cache-Usage
• Less Lock- & Update-Overhead
• Storage-Optimization