Extended DB Features  «Prev  Next»
Lesson 2 Partitioning explained
ObjectiveDescribe table partitioning.

Oracle Table Partitioning explained

The Oracle database gives you access to information based on the logical structure of that information, but the data within an Oracle database is stored in physical structures. Just as a tablespace is a logical concept, the data within the tablespace is stored within one or more physical data files.
Partitioned tables
The concept of a partitioned table is analogous to the relationship between a tablespace and its data files.
You can have one or more data files associated with a tablespace. Even though the data files are invisible to you, all of your database maintenance operations occur at the level of the tablespace. A partition of a table is a section of that table. The different partitions in the table are transparent to a user executing an SQL statement against the table. However, unlike the tablespace and data file relationship, you have direct control over how the table is partitioned. The Oracle database can take advantage of the different partitions of a table to improve performance and increase maintainability of the table.
The structure of a partitioned table is graphically displayed in the following MouseOver.

Partitioning offers these advantages:

  1. It enables data management operations such as data loads, index creation and rebuilding, and backup/recovery at the partition level, rather than on the entire table. This results in significantly reduced times for these operations.
  2. It improves query performance. In many cases, the results of a query can be achieved by accessing a subset of partitions, rather than the entire table. For some queries, this technique (called partition pruning) can provide order-of-magnitude gains in performance.
  3. It significantly reduces the impact of scheduled downtime for maintenance operations.
  4. Partition independence for partition maintenance operations lets you perform concurrent maintenance operations on different partitions of the same table or index. You can also run concurrent SELECT and DML operations against partitions that are unaffected by maintenance operations.
  5. It increases the availability of mission-critical databases if critical tables and indexes are divided into partitions to reduce the maintenance windows, recovery times, and impact of failures.
  6. Parallel execution provides specific advantages to optimize resource utilization, and minimize execution time. Parallel execution against partitioned objects is key for scalability in a clustered environment. Parallel execution is supported for queries as well as for DML and DDL.

Faster Data Access

Partitioning enables faster data access within an Oracle database. Whether a database has 10 GB or 10 TB of data, partitioning can improve data access by orders of magnitude. Partitioning can be implemented without requiring any modifications to your applications. For example, you could convert a nonpartitioned table to a partitioned table without needing to modify any of the SELECT statements or DML statements which access that table. You do not need to rewrite your application code to take advantage of partitioning.
You can only use partitioning if you have purchased the partitioning option for the Oracle8 Enterprise Edition database.
The next lesson examines the advantages of using partitioned tables.