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.
To answer directly to your last question: no, I don't think that they are equivalent. Think of a very big table (tens of GB) that will need to be searched for a day's data. Using daily partitions you will be scanning only that day's data, without any space penalty, while an index is a separate structure that might not hold enough information (and also takes space, which is a concern when speaking about big databases) or might not be selective enough.
Anyway, indexes and partitions are not competing for the same benefits in usage and performance. You should find the balance in your database.
