Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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?

share|improve this question
2  
Instead of thinking of indexing and partitioning as either-or solutions for performance improvement, you should be thinking of both as tools that can be used separately or in conjunction to achieve performance improvements in your database. –  ScottCher Mar 25 '11 at 20:11

2 Answers 2

up vote 16 down vote accepted

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.

share|improve this answer
3  
I you are creating partitions. Be sure to chose right storage device for each partitions. Put the actual data partition on a high speed drive and the older one other type. –  Nico Mar 18 '11 at 22:50

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.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.