Partial Index in Fragment Expressions
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;
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.
select count(*) from tab1 where n < 1000 and n2 is not null;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;
select fname, lname from customer
where zipcode >= '94117'
and state='CA';