Partitioning is a way to spread a single table's data over multiple partitions. Each partition can be on a seperate FileGroup.
Advantages of using Partition :
1. Faster and Easier Data Loading :
A partition table enables us to load the data to an empty table that's not in use by the LIVE data, so it will have minimal impact on concurrent LIVE operations. Anyway, there will be an impact to the I/O subsystem, but if we also have separate filegroups on different physical disks, even this has a minimal impact on overall system performance. Once the data loaded to the new table, we can perform a SWITCH to enable to the live data. The SWITCH is nothing but simple metadata change. So, Partitioning is the best way to load large amount of data with minimul impact.
2. Easier Data Archival :
We often want to move the old data to different storage location other than the actual data location. The SWITCH operation is the fast because all it does is change metadata. So, it will not move the actual data.
3. Faster Queries :
The Query Optimizer will not search through partitions that it knows won't hold any data. Eliminating search through the Partitions is called as Partition Elimination.
Advantages of using Partition :
1. Faster and Easier Data Loading :
A partition table enables us to load the data to an empty table that's not in use by the LIVE data, so it will have minimal impact on concurrent LIVE operations. Anyway, there will be an impact to the I/O subsystem, but if we also have separate filegroups on different physical disks, even this has a minimal impact on overall system performance. Once the data loaded to the new table, we can perform a SWITCH to enable to the live data. The SWITCH is nothing but simple metadata change. So, Partitioning is the best way to load large amount of data with minimul impact.
2. Easier Data Archival :
We often want to move the old data to different storage location other than the actual data location. The SWITCH operation is the fast because all it does is change metadata. So, it will not move the actual data.
3. Faster Queries :
The Query Optimizer will not search through partitions that it knows won't hold any data. Eliminating search through the Partitions is called as Partition Elimination.
No comments:
Post a Comment