Mastering Oracle SQL, 2nd Edition

Chapter 10. Partitioning

Over the past 15 years, hard disk capacities have evolved from around 10 MB to over 100 GB, and capacities are still growing. Disk arrays are fast approaching the 100 terabyte range. No matter how much storage is available, however, there is always a way to exhaust it. As databases grow in size, day-to-day operations become more and more challenging. For example, finding the time and resources to rebuild an index containing 100 million entries can prove quite demanding. Prior to Oracle8, database administrators would meet this challenge by manually breaking a large table into several smaller tables. Although the pieces could be hidden behind a special type of view (called a partition view) during a query, all DML statements had to be performed against the individual tables, thereby exposing the partitioning scheme to the database developers and users.

Starting with Version 8.0, Oracle provided a means for breaking a table into multiple pieces while preserving the look and feel of a single table. Each piece is called a partition, and, although every partition must share the same columns, constraints, indexes, and triggers, each partition can have its own unique storage parameters. While administrators generally deal with individual partitions when allocating storage and performing backups, developers may choose to deal with either the entire table or with individual partitions.

Partitioning Concepts

Database designers and administrators have been partitioning ...