To Partition or Not to Partition -- Partitioning Features in Oracle 8i
- To Partition or Not to Partition
- Oracle Partitioning Options
Oracle Corporation introduced partitioning with the release of the Oracle8 database software. Release Oracle8i has since enhanced these partitioning features. Oracle expert Dan Hotka discusses exactly what partitioning is and the useful features of the Oracle implementation.
This paper and presentation are based on personal experience, Oracle Server documentation, and Oracle8 course materials. Parts of this article are adapted from the author's work in Oracle8i from Scratch(Que, 2000, ISBN 0789723697) and Oracle8 Server Unleashed (SAMS, 1998, ISBN 0672312077).
In my experience, the more you divide disk I/O across disk drives, the more responsively SQL engines perform. This is the best reason for partitioning Oracle tables and/or indexesto break a large object into smaller pieces that are more manageable in both performance and availability.
Partitioning greatly enhances database performance, which can be monitored and adjusted in a variety of ways. Read/write activity can be load balanced among disk drives/disk arrays. The Oracle cost-based optimizer recognizes partitions and can select information only from the correct partitions with the specific data being requested. The Oracle Parallel Server can divide work based on the number of partitions.
With partitioned disks, availability is improved, backups are easier, recovery is shorter. High availability is the single goal of most computer systems. Recoveryand time to recoveris always an issue. Downtime for maintenance or backups is impossible with some applications, but machine failures are inevitable. Partitions can be backed up online, individually, and you can choose to back up only the partitions containing changes. This makes the backup/recovery process much quicker. If recovery is needed, the other, unaffected partitions are still available and online.
Administration and tuning are also much easier with partitions. Oracle partitioning has many features that make the partitions easy to create, move, split, and so on. You can even swap an existing nonpartitioned table structure and data into a partitioned table. Tuning is enhanced with the additional indexing features available.