Partitioned Tables   «Prev  Next»

Lesson 3 Advantages of Partitioning
Objective Explain the advantages of partitioning.

Advantages of Database Partitioning Tables in Oracle

Table partitioning delivers advantages in three main areas
  1. performance,
  2. maintenance, and
  3. availability.

Two Types of Advantages

  1. 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.
  2. 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.

Improve Tablespace 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 the following series of images:

Advantages of Oracle Partitioning

1) A table can be divided into multiple partitions
1) A table can be divided into multiple partitions

2) A select statement can be executed against the complete table
2) A select statement can be executed against the complete table.

3) The Oracle database will only access the partition that contain data as specified by the WHERE clause of the query 
SELECT * FROM TABLE A WHERE ...
3) The Oracle database will only access the partition (or partitions) that contain data as specified by the WHERE clause of the query

4) Backup and recovery operations can be executed against individual partitions
4) Backup and recovery operations can be executed against individual partitions


5) This reduces the overall time needed for these operations 5) Backup and recovery operations can be executed against individual partitions.
This reduces the overall time needed for these operations

6) If the partitions are stored in tablespaces
6) If the partitions are stored in different tablespaces, one partition can become unavailable.

7) While the other partitions remain available
7) 
SELECT * FROM TABLE A WHERE
While the other partitions remain available.

  1. A table can be divided into multiple partitions
  2. A select statement can be executed against the complete table.
  3. The Oracle database will only access the partition (or partitions) that contain data as specified by the WHERE clause of the query
  4. Backup and recovery operations can be executed against individual partitions
  5. This reduces the overall time needed for these operations
  6. If the partitions are stored in different tablespaces, one partition can become unavailable.
  7. While the other partitions remain available.


Partitioning is the Foundation for Information Lifecycle Management

Information Lifecycle Management (ILM) is a set of processes and policies for managing data throughout its useful life. One important component of an ILM strategy is determining the most appropriate and cost-effective medium for storing data at any point during its life time:
  1. newer data used in day-to-day operations is stored on the fastest, most highly-available storage tier,
  2. while older data which is accessed infrequently may be stored on a less-expensive and less-performant storage tier.
Older data may also be updated less frequently in which case it makes sense to compress and store the data as read-only.
Oracle Database provides the ideal environment for implementing your ILM solution. Oracle supports multiple storage tiers, and since all of the data remains in the Oracle database, the use of multiple storage tiers is completely transparent to the application and the data continues to be completely secure. Partitioning provides the fundamental technology that enables data in tables to be stored in different partitions.
Although multiple storage tiers and sophisticated ILM policies are most often found in enterprise-level systems, most companies and most databases need some degree of information lifecycle management. The most basic of ILM operations, archiving older data and purging or removing that data from the database, can be orders of magnitude faster when using partitioning.
Partitioning can also be used with Partitioning Multiple Disks for added advantages.

Partitioning and multiple Disks in Oracle

You can add to the inherent advantages of partitioning by using multiple disks for the different tablespaces that contain the partitions. Placing data on separate disk drives eliminates disk head contention for data in the different partitions. You also can use RAID disk arrays to stripe a partition across multiple disks and add additional I/O threads to operate on them, or you can limit a partition to a single disk to reduce the impact of disk failure on the table.

Two-Terabyte Disk Support for Installing and Booting the Solaris OS

Solaris 10 10/09: In previous Solaris releases, you could not install and boot the Solaris OS from a disk that was greater than 1 TB in size. In this Solaris release, you can install and boot the Solaris OS from a disk that is up to 2 TB in size. In previous releases, you also had to use an EFI label for a disk that is larger than 1 TB. In this release, you can use the VTOC label on any size disk, but the addressable space by the VTOC is limited to 2 TB. The Solaris disk drivers and disk utilities have been updated to provide the following support:

Installing and Booting

Installing and booting the Solaris OS on a two-terabyte disk must be connected to a system that runs a 64-bit kernel, with a minimum of 1 GB of memory. You can use the format -e utility to label a disk of any size with a VTOC label, but the addressable space is limited to 2 TB. The default label that is used by the format utility and the installation software for a disk that is less than 2 TB in size is a VTOC label.
You can use the fdisk utility on a disk that is greater than 1 TB on x86 systems. Support is added for up to 2-TB partitions in the MBR for non-EFI partition types. This support means that Solaris partitions can go up to 2 TB. Other non-EFI partitions may be subject to a limit depending on partition type. When the fdisk utility is run on a disk that is greater than 2 TB in size, a warning message is displayed to indicate that you cannot create a non-EFI partition that is greater than 2 TB. The Solaris Volume Manager software has been modified to create metadevices that support physical disks with VTOC labels up to 2 TB in size. Keep in mind that you cannot move a disk over 1 TB with a legacy MBR or a legacy VTOC to a previous Solaris release. EFI labeled disks continue to work as in previous Solaris releases. For more information about the EFI label changes in this release, see EFI Disk Label.


Partitioning Advantages - Quiz

Click the Quiz link below to answer a few questions about table partitions.
Partitioning Advantages - Quiz
The next lesson shows you how to partition a table based on a range of values.

SEMrush Software