DB2 OS/390 V7

I want to create an index whose entries correspond just a selected values (range) of one of the column used to define the index.
In my case, I have the tbl ORDER, with the column ORDER_ST (status). The values for status are: 1=Pending, 2=In progress, 3=Delivered.
Frequenly, I want to retrieve all rows pending or in progress (less than 10% of total of tbl's rows).
What is the better way to solve this requirement?

Thanks, ALOZ.

The main consideration is whether DB2 will even use such an index. DB2 will use an index if it can reduce the number of pages (typically 4K in size) that need to be accessed.

If the values for ORDER_ST are randomly distributed throughout the table, and the row size is 400 bytes or less, then on average there is at least one qualifying row on each 4K page (since you said pending and in progress orders are 10% of the total). DB2 will not likely use an index on ORDER_ST in this situation. This scenario is more likely if the table is clustered by customer number, or some other similar column.

However, if the pending and in progress orders tend to be physically at the end of the table (cluster ratio of index on ORDER_ST is relatively high), then DB2 would be more inclined to use the index since it can reduce the number of 4K pages accessed. If the table was physical clustered by an index on ORDER, and ORDER was based on a next available number (or append was used to insert new rows), the cluster ratio for ORDER_ST might be coincidently high even though it is not the clustering index because more recent orders tend to be the ones not shipped yet. You can check the cluster ratio of the index in the catalog after running runstats.

For queries, I would probably try “ORDER_ST < ‘3’ ” as the predicate instead of “ORDER_ST in (‘1’,’2’)”.

Make sure you capture detail runstats on column distribution (otherwise DB2 will assume each of the three values occupies 33.3% of the table). For example:

RUNSTATS INDEX (index-qualifier.index-name)
KEYCARD
FREQVAL NUMCOLS 1 COUNT 3

Using the explain will tell you whether the index on ORDER_ST will be used for the SQL statements in your application. But if in spite of all your attempts to make adjustments, the index is never used, you would be better off just dropping the index.

Depending upon the table size and key structures, you might want to consider spliting up the table into (3) partitions based on order_status, that way you can address rows for each order_status separately going by partition.

...or you can go even further. Create 3 tables and then create a view containing UNION, that links them together! Good thing is, that you can have very different indexes in each 'partition' this way.

Cheers, Bill