Partial Index in Fragment Expressions

Starting 14.10xC2 onwards, Informix supports FRAGMENT BY EXPRESSION partial index.

A partial index is an index built over a subset of a table. The subset is defined by a conditional expression (called the predicate of the partial index). The index contains entries for only those table rows that satisfy the predicate.

Partial indexes avoid indexing common values. As the query searching for a common value will not use the index, there is no point in keeping those rows in the index. This reduces the size of the index, which will speed up queries that do use the index. It will also speed up many table update operations because the index does not need to be updated in all cases.

Example 1: Setting up a Partial Index to Exclude Common Values

create table tab1 (n int, n2 int, n4 int, cc char(16));
-- insert 10,000 rows
with cte(n) as (
	select 1 as n  
	union all
	select n+1 from cte where n < 10000
	)
insert into tab1 
  select n,
  	case when mod(n,10) == 0 then n else null end,
  	mod(n,4),
  	n from cte;
To create a partial index that suits the above example for non-null values, create index as:
create index idx1 on tab1(n) fragment by expression
        (n2 is null) in dbs1 INDEX OFF,
	remainder in dbs2;

“oncheck -pk | grep ‘^Key’ | wc -l" shows only 1000 rows are indexed.
The query that can use this index would be: 
select count(*) from tab1 where n < 1000 and n2 is not null;
The query that cannot use this index would be: 
select count(*) from tab1 where n < 1000;

Example 2: Setting up a Partial Index to Exclude Un-wanted Values

If you have a table that contains customers from different states, where one specific state records take up a small fraction of the total table and yet those are the most-accessed rows, you can improve performance by creating an index on just that specific state rows. The command to create the index:

create index zip_ix on customer(zipcode)
    fragment by expression
        (state = 'CA') in dbs1,
        remainder in INDEX OFF;
The query that can use this index would be: 
select fname, lname from customer 
        where zipcode >= '94117'
        and state='CA';
Note: A partial index can be used in a query only if the system can recognize that the WHERE condition of the query mathematically implies the predicate of the index..