Partitioning with Oracle Database 11g Release 2

Oracle White Paper-Partitioning with Oracle Database 11g Release 2
An Oracle White PaperSeptember 2009
Partitioning with
Oracle Database 11g Release 2Oracle White Paper-Partitioning with Oracle Database 11g Release 2
Partitioning - Concepts........................................................................ 3
Introduction...................................................................................... 3
Benefits of Partitioning .................................................................... 3
Partitioning - Modeling for your Business............................................ 7
Basic Partitioning Strategies............................................................ 7
Partitioning Extensions..................................................................... 8
Partition Advisor............................................................................... 9
Partitioning Strategies and Extensions at a Glance....................... 10
Information Lifecycle Management with Partitioning.......................... 11
Conclusion......................................................................................... 11Oracle White Paper-Partitioning with Oracle Database 11g Release 2
Partitioning - Concepts
Introduction
Oracle Partitioning, first introduced in Oracle 8.0 in 1997, is one of the most important and successful functionalities of the Oracle database, improving the performance, manageability, and availability for tens of thousands of applications. Oracle Database 11g Release 2 introduces the th9 generation of partitioning, enabling customers to model even more business scenarios and making Partitioning easier to use, enabling Partitioning "for the masses". Oracle Database 11g Release 2 continues to enhance Oracle Partitioning, protecting our customers' investment in partitioning for over a decade.
Benefits of Partitioning
Partitioning can provide tremendous benefits to a wide variety of applications by improving manageability, performance, and availability. It is not unusual for partitioning to improve the performance of certain queries or maintenance operations by an order of magnitude. Moreover, partitioning can greatly reduce the total cost of data ownership, using a "tiered archiving" approach of keeping older relevant information still online on low cost storage devices. Oracle Partitioning enables an efficient and simple, yet very powerful approach when considering Information Lifecycle Management for large environments. Partitioning also enables database designers and administrators to tackle some of the toughest problems posed by cutting-edge applications. Partitioning is a key tool for building multi-terabyte systems or systems with extremely high availability requirements.
Basics of Partitioning
Partitioning allows a table, index or index-organized table to be subdivided into smaller pieces. Each piece of the database object is called a partition. Each partition has its own name, and may optionally have its own storage characteristics. From the perspective of a database administrator, a partitioned object has multiple pieces that can be managed either collectively or individually. This gives the administrator considerable flexibility in managing partitioned object. However, from the perspective of the application, a partitioned table is identical to a non-partitioned table; no modifications are necessary when accessing a partitioned table using SQL DML commands.
3Oracle White Paper-Partitioning with Oracle Database 11g Release 2
Figure 1: Application and DBA perspective of a partitioned table
Database objects - tables, indexes, and index-organized tables - are partitioned using a 'partitioning key', a set of columns that determine in which partition a given row will reside. For example the sales table shown in figure 1 is range-partitioned on sales date, using a monthly partitioning strategy; the table appears to any application as a single, 'normal' table. However, the DBA can manage and store each monthly partition individually, potentially using different storage tiers, applying table compression to the older data, or store complete ranges of older data in read only tablespaces. Irrespective of the chosen index partitioning strategy, an index is either coupled or uncoupled with the underlying partitioning strategy ... [download for more]