| Lesson 3 | Advantages of Partitioning |
| Objective | Explain the advantages of partitioning. |
1) Performance, 2) Maintenance, and 3) Availability
Advantages of Database Partitioning Tables in Oracle
Table partitioning delivers advantages in three main areas
- performance,
- maintenance, and
- availability.
Performance advantages
A table is partitioned based on a criterion such as the value for a particular column. If a query requests data with a particular selection condition that would eliminate a complete partition, Oracle automatically ignores that partition in executing the query. In this way, you can partition a large table to get the advantages of a smaller table. For instance, you may have a very large table with all of the orders for a year. Most queries, though, only request data for a single month. You could partition the table by month, so that these common queries would, in effect, be accessing a smaller table, while the complete set of data still would be available.
The Oracle cost-based optimizer also takes this into consideration when deciding how to execute an SQL statement.
Maintenance advantages
Most maintenance operations can be performed on a single partition. You can backup or recover a partition rather than the entire table. In this way, you can significantly reduce the time required to perform maintenance operations.
In the above example of a table partitioned by month, you could perform daily backups on the current month's partition, and less frequent backups on previous months. In an extremely large database, this can make the difference between having enough time to complete a backup in off-business hours or not.
Availability
You can also use partitioning to place partitions into different tablespaces to improve availability. One tablespace can go down without affecting the other tablespaces. If a tablespace becomes unavailable, the other tablespaces and their partitions are still available.
Returning to the table partitioned by months, if the tablespace that contains the partition for January's data goes down, all the other partitions are still available. Only queries that require January data will fail to execute.
Splitting data into different partitions is also a time saver. When you reduce the amount of data in each partition, you also reduce the amount of time required to recover that partition.
The advantages of partitioning are described in this SlideShow:
- A table can be divided into multiple partitions
- A select statement can be executed against the complete table.
- The Oracle database will only access the partition (or partitions) that contain data as specified by the WHERE clause of the query
- Backup and recovery operations can be executed against individual partitions
- This reduces the overall time needed for these operations
- If the partitions are stored in different tablespaces, one partition can become unavailable.
- While the other partitions remain available.
Partitioning Advantages - Quiz
