Oracle9i Partitioning Options

Oracle8 introduced table partitioning with a feature known as range partitioning. Range partitioning means separating the rows from a table into various predefined tablespaces by a key, known as the partition key. The table can be accessed like any other table by its table name, or each partition can be accessed individually. An example of this is a quarterly report on a particular quarter in which the table is partitioned by date so that a quarter's worth of data resides in each tablespace. This report, knowing that it was going to use only data from this one “quarterly” tablespace, could just access this one tablespace, saving the optimizer and Oracle some work.

Each tablespace can be backed up and restored independently from the others.

Oracle8i built on this partitioning feature by offering hash partitioning and composite partitioning. Hash partitioning is an alternative to range partitioning when no predictable data pattern, such as a date field, exists. Hash partitioning evenly distributes the rows, based on a hash key, across the defined tablespaces.

Composite partitioning, on the other hand, is a combination of range and hash partitioning. In this process, the table is first distributed across tablespaces based on a range of keys, and then each of these range partitions is further subdivided across subpartitions (where the partition is actually divided a predetermined number of times). Then, rows are evenly allocated across these subpartitions by a hash key.

Oracle9i introduces LIST partitioning. This gives Oracle the ability to separate data across partitions based on a character string. List partitioning does not support any kind of order, it simply goes by a list of items that is to go in one partition or another.