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.