What is table partitioning?

Question

In which case we should use table partitioning?

Solution 1

Partitioning enables tables and indexes or index-organized tables to be subdivided into smaller manageable pieces and these each small piece is called a "partition".

For more info: Partitioning in Oracle. What? Why? When? Who? Where? How?

Solution 2

An example may help.

We collected data on a daily basis from a set of 124 grocery stores. Each days data was completely distinct from every other days. We partitioned the data on the date. This allowed us to have faster searches because oracle can use partitioned indexes and quickly eliminate all of the non-relevant days. This also allows for much easier backup operations because you can work in just the new partitions. Also after 5 years of data we needed to get rid of an entire days data. You can "drop" or eliminate an entire partition at a time instead of deleting rows. So getting rid of old data was a snap.

So... They are good for large sets of data and very good for improving performance in some cases.

Solution 3

When you want to break a table down into smaller tables (based on some logical breakdown) to improve performance. So now the user can refer to tables as one table name or to the individual partitions within.

Comments

[David Aldridge]

-1, because the benefits of partitioning are not necessarily related to data volumes. Partitioning can make performance worse just as easily as it can make it better.