CODEWITHSUNDEEP
sqlpartitioningdatabase-partitioningdatabase-table
P Sharma
P Sharma

Reputation: 2728

What is table partitioning?

In which case we should use table partitioning?

Upvotes: 32

Views: 25583

Answers (5)

OMG Ponies
OMG Ponies

Reputation: 332661

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".

Upvotes: 26

David Aldridge
David Aldridge

Reputation: 52386

i) It is the subdivision of a single table into multiple segments, called partitions, each of which holds a subset of values.

ii) You should use it when you have read the documentation, run some test cases, fully understood the advantages and disadvantages of it, and found that it will be of benefit.

You are not going to get a complete answer on a forum. Go and read the documentation and come back when you have a manageable problem.

Upvotes: -6

Philip Schlump
Philip Schlump

Reputation: 3144

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.

Upvotes: 34

Wade73
Wade73

Reputation: 4509

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.

Upvotes: 9

Kico Lobo
Kico Lobo

Reputation: 4404

Table partitioning consist in a technique adopted by some database management systems to deal with large databases. Instead of a single table storage location, they split your table in several files for quicker queries.

If you have a table which will store large ammounts of data (I mean REALLY large ammounts, like millions of records) table partitioning will be a good option.

Upvotes: 0

Related Questions