Oracle partitioning and local Indexex (12c)

Question

I have a table created which is partitioned by year and I have the below two local non unique indexes created on the table

idx1 : (year, wk, pd, sku) idx2 : (sku, str )

My undersatnding is that idx2 is redundant and and you could just create one index with (year,wk, pd, sku, str) to take adavantage of skip scans. Any thoughts or comments?

Answer 1

Indexing the partition key is redundant as partition pruning will effectively "index" that field, and it pruning occurs before any index reads. At which point, skip scans will act normally, assuming the optimizer doesn't score the skip scan higher than a full partition scan.

Answer 2

Assuming you have a query specifying only sku and str, then idx2 is not redundant.

Using idx1, you've still got at least 52 weeks x ?? (don't know what pd is...) = 52 or more index sub-trees for an INDEX SKIP SCAN to look through, per partition.

An INDEX SKIP SCAN on idx1 will do 52+ index probes. An INDEX RANGE SCAN on idx2 will to one probe per partition (which you could cut down to 1 overall probe by making that a global index).

So, idx2 offers benefits. Whether those benefits justify the cost of a 2nd index on the table depend on your usage patterns (writes vs reads) and performance goals.