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 only takes a minute to sign up.
Sign up to join this community
Maybe is stupid question, but I must ask to be clear with that.
I know that table partitioning is powerful mechanism of speeding data search when partitions is placed on different disks.
What I need to know what I can get if I place partitions on same disk?
It can help query performance by employing partition elimination. This means large sections of big tables can be ignored when looking for values which means much less IO. Index alignment needs to be looked into when partitioning. See details here
You can break your backups by partition. This can be useful if you are struggling to complete your backups in time. See here for details
Index rebuilds can be done at the partition level instead of the whole table.
Large inserts can be done with partition switching.
Queries can also run parallel across partitions.
James Anderson got most of it. I just want to add one more.
Purging is a huge reason we are working on Partitioning.
If you indexes are aligned, then you can do partition switching. For us that means that we can take older partitions and just switch them out to different table and then drop that table.
The actual switch allows me to remove over a million rows in less than a millisecond.
There are trade offs (some significant ones), but deleting data for large transaction systems is sometimes a very hard story.
My company has a SAN and the System Admins will not even hear of allowing storage anywhere else.
Despite the fact that my partitions will be on the same disk, I still am currently working in implementing it just for the data purging story alone.
