Index vs. partition

Why is it not possible to achieve performance improvements with indexes only, so that other techniques like table partitioning becomes necessary? The question relates only to performance, of course different partitions can be put into different tablespaces, which has other effects that cannot be achieved with indices.

Or in other words, only performance wise: is it possible to achieve same performance improvements with indices as with partitioning of tables?

No, partitioning allows some table scans to be restricted to a particular partition. Indexes tend to be useless if you will be returning more that 2 to 4 percent of the table's data. If your selection criteria allows the query to be localized to particular partition, then the other partitions won't need to be scanned.

It might be possible for the optimizer to choose to use table scanning for some partitions and indexes for others. I haven't seen any explain plans that indicate that is happening.

If you have a lot of historical data, then queries for current data can be constructed to limit the partitions that are considered to those containing current data.

StackExchange Database Administrators Q#1699, answer score: 21