Mastering Oracle SQL

Chapter 10. Partitions, Objects, and Collections

Oracle8 introduced a host of new features to support large databases and object-relational constructs. The Oracle8i and Oracle9i releases further expanded and refined these areas. This chapter explores partitioning, which addresses the needs of large database implementations, and objects and collections, which facilitate the storage and propagation of complex datatypes.

Table Partitioning

Over the past 15 years, hard disk capacities have evolved from around 10 megabytes to over 100 gigabytes, 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, ...