Particionamiento En Oracle

Oracle White Paper—Partitioning with Oracle Database 11g Release 2

An Oracle White Paper September 2009

Partitioning with Oracle Database 11g Release 2

Oracle White Paper—Partitioning with Oracle Database 11g Release 2

Partitioning – Concepts........................................................................ 3Introduction...................................................................................... 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......................................................................................... 11

Oracle White Paper—Partitioningwith 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 9th 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 improvingmanageability, 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 anefficient 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 ofPartitioning

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 orindividually. 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.

3

Oracle White Paper—Partitioning with Oracle Database 11g Release 2

Figure 1: Application and DBAperspective 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,...