Partitioned Secondary Index vs Non-Partitioned Index for Claims
CLAIMS is a 1.2-billion-row, 900-GB table in a Db2 12 subsystem. It is range-partitioned by claim_month into 72 monthly partitions and has two indexes: a partitioning index on claim_id and a 400-GB non-partitioned index on policy_id. Workload: 70% of transactions fetch one claim by claim_id; 25% list claims for a policy_id within a claim_date range; 5% batch scans by claim_month. The 25% queries average 12 rows but arrive at 300 per second. A REORG of one partition currently rebuilds the entire 400-GB index and takes nine hours, exceeding the four-hour batch window. Insert contention is growing on the latest partition because claim_id is not chronological. The design team proposes either a partitioned secondary index on policy_id or keeping the non-partitioned index and adding a covering index. They ask for a recommendation, including utility impact and query risk.
1. Compare a partitioned secondary index (DPSI) on policy_id with the existing non-partitioned index for the 25% policy queries. What determines whether the DPSI is a win?
Read the worked answer
A DPSI is partitioned by the table partitioning key, so a policy's claims are spread across the monthly partitions that contain them. If the query supplies a claim_date range that maps to a few claim_month partitions, Db2 can prune to those partitions and probe a smaller index in each. If the query supplies only policy_id, Db2 must probe all 72 partitions, and at 300 queries per second that is 21,600 partition probes per second before fetching any rows. The non-partitioned index gives one index scan and then fetches, but it is 400 GB and rebuilds as a whole during REORG. The DPSI becomes a clear win when the policy queries almost always include claim_date, the date range is narrow, and REORG pain is the dominant problem. It is a poor choice if the policy queries frequently omit claim_date or span many months. Before deciding, capture the actual predicate patterns and measure partition probes; do not assume that a DPSI is automatically cheaper just because it is partitioned.
2. What index and table design changes would you make for insert contention and REORG, and what are the tradeoffs?
Read the worked answer
Use a DPSI on (policy_id, claim_date) so that policy queries can use both columns for pruning and index-only access, and so that REORG can rebuild only the affected partition's index. Keep the partitioning index on claim_id for the 70% single-claim lookups. The tradeoff is that policy queries without claim_date must probe every partition, so application predicates must be enforced. For insert contention on the latest partition, recognize that range partitioning by month concentrates all current inserts in one partition; a DPSI does not remove that. Reduce page-level contention with appropriate PCTFREE/FREEPAGE and, if this is a data sharing member, consider MEMBER CLUSTER. Avoid adding a second wide covering index just to avoid fetches: include columns enlarge every leaf page and increase insert and REORG cost. If a covering index is still justified, limit INCLUDE columns to stable projected columns and measure the index size increase. Reassess partition granularity only if monthly partitions become too large; more partitions spread maintenance but do not eliminate the current-month insert hotspot.
3. The team wants a covering index to avoid fetches. What columns would you include, and what could go wrong?
Read the worked answer
For the policy list query, the key should support the predicates: policy_id equality and claim_date range, so (policy_id, claim_date). Include only columns that the query projects and that are stable, such as claim_status and amount, and only if the resulting index remains reasonably narrow. Do not include wide text or LOB columns. The risks are real: every included column increases leaf-page size, reduces rows per page, increases logging and insert cost, and makes REORG longer. An index-only access path can also be lost if the application adds a projected column, so the index becomes a maintenance liability without the intended benefit. In addition, an index-only scan still has to probe the right partitions; a covering DPSI does not fix a missing claim_date predicate. Test with EXPLAIN and measure getpages and index leaf size before committing, and compare the cost against accepting a small number of fetches for 12-row result sets.