What is table partitioning?

sql partitioning database-partitioning database-table
P Sharma picture P Sharma · Nov 30, 2009 · Viewed 17.8k times · Source

In which case we should use table partitioning?

Answer

Philip Schlump picture Philip Schlump · Nov 30, 2009

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.

Related questions

How to select rows from partition in MySQL

I made partition my 300MB table and trying to make select query from p0 partition with this command SELECT * FROM employees PARTITION (p0); But I am getting following error ERROR 1064 (42000): You have an error in your SQL syntax; check the …

Read More
Find Partition Schema Definitions in SQL Server Database

I have access to a database and I need to know the Partition Scheme definitions in the database. i.e. I need to know the partition scheme name, which Partition function is it using, what file groups are the partitions …

Read More
In Oracle SQL, can I query a partition of a table instead of an entire table to make it run faster?

I would like to query a table with a million records for customers named 'FooBar' that have records dated on 7-24-2016. The table has 10 days of data in it. select * from table where customer = 'FooBar' and insert_date between …

Read More