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?

How many distinct values of `pd` will there be in each year/week on average? — Matthew McPeak, Oct 12 '17 at 20:28
That's a lot, per week. It means, even if given a year and a SKU, the `INDEX SKIP SCAN` needs to do 676 index probes. Still probably better than a `FULL SCAN`, but I would much rather do a range scan on `IDX2`. — Matthew McPeak, Oct 12 '17 at 21:40
Have a look at [Index Monitoring](https://oracle-base.com/articles/10g/index-monitoring), this should show you whether index is used or not. — Wernfried Domscheit, Oct 13 '17 at 05:58

Matthew McPeak · Answer 1 · 2017-10-12T20:27:27.757

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.

score 0 · Accepted Answer · answered Oct 12 '17 at 20:19

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.

Oracle partitioning and local Indexex (12c)

2 Answers2