To Partition or Not to Partition -- Partitioning Features in Oracle 8i

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.

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

Why Partition?

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 indexesto 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. Recoveryand
time to recoveris 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.