Oracle 8i Partitioning Methods
By Vijayabaskar Srinivasan, Datacons

What Is Partitioning?

Partitioning addresses the key problem of supporting very large tables and indexes by allowing you to decompose them into smaller and more manageable pieces called partitions. Once partitions are defined, SQL statements can access and manipulate the partitions rather than entire tables or indexes. Partitions are especially useful in data warehouse applications, which commonly store and analyze large amounts of historical data.

Partitioning Methods

Two primary methods of partitioning are available: range partitioning, which partitions the data in a table or index according to a range of values, and hash partitioning, which partitions the data according to a hash function. Another method, composite partitioning, partitions the data by range and further subdivides the data into subpartitions using a hash function.

A separate segment stores each partition of a range-partitioned or hash-partitioned table or index, and each subpartition of a composite-partitioned table or index. The partitions of a composite-partitioned table or index are logical structures only--they do not occupy separate segments because their data is stored in the segments of their subpartitions.

Optionally, you can store each partition (or subpartition of a composite-partitioned table or index) in a separate tablespace, which has the following advantages:

• You can contain the impact of data corruption.
• You can back up and recover each partition (or subpartition) independently.
• You can map partitions (or subpartitions) to disk drives to balance the I/O load.

Advantages of Partitioning

This section identifies the classes of databases that could benefit from the use of partitioning, and characterizes them in terms of the problems they present:

• Very Large Databases (VLDBs)
• Reducing Downtime for Scheduled Maintenance
• Reducing Downtime Due to Data Failures
• DSS Performance
• I/O Performance
• Disk Striping: Performance versus Availability
• Partition Transparency

Range Partitioning

Range partitioning maps rows to partitions based on ranges of column values. Range partitioning is defined by the partitioning specification for a table or index:

PARTITION BY RANGE ( column_list )

and by the partitioning specifications for each individual partition:
VALUES LESS THAN ( value_list )

• column_list is an ordered list of columns that determines the partition to which a row or an index entry belongs.
  • These columns are called the partitioning columns.
  • The values in the partitioning columns of a particular row constitute that row's partitioning key.
• value_list is an ordered list of values for the columns in column_list.
  • Each value in value_list must be either a literal or a TO_DATE() or RPAD() function with constant arguments
  • The value_list contained in the partitioning specification for each partition defines an open (noninclusive) upper bound for the partition, referred to as the partition bound.
  • The partition bound for each partition must compare less than the partition bound for the next partition.

In each partition, all rows (or rows pointed to by index entries) have partitioning keys that compare less than the partition bound for that partition. Unless the partition is the first partition in the table or index, all of its partitioning keys also compare greater than or equal to the partition bound for the previous partition

CREATE TABLE sales
    ( invoice_no NUMBER,
      sale_year  INT NOT NULL,
      sale_month INT NOT NULL,
      sale_day   INT NOT NULL )
  PARTITION BY RANGE (sale_year, sale_month, sale_day)
    ( PARTITION sales_q1 VALUES LESS THAN (1997, 04, 01)
        TABLESPACE tsa,
      PARTITION sales_q2 VALUES LESS THAN (1997, 07, 01)
        TABLESPACE tsb,
      PARTITION sales_q3 VALUES LESS THAN (1997, 10, 01)
        TABLESPACE tsc,
      PARTITION sales_q4 VALUES LESS THAN (1998, 01, 01)
        TABLESPACE tsd );