Oracle Database Administration: The Essential Refe

Partitioned tables

Partitioned tables, which are available beginning with Oracle8, allow a table to be created across multiple subtables, each of which holds a particular subset of the table data. For example, a table could be partitioned by year, with all data from 1998 in one partition, all 1999 data in another, and so on. Partitioning is particularly useful for large tables, since queries involving only an identifiable subset of data can operate on the data in the appropriate partitions without accessing other partitions. For example, updating 1999 records would only require Oracle to perform I/O operations on the 1999 partition of the table. Partitioning is specified in the CREATE TABLE statement. In order to use this feature, you must:

• Identify the data field that will define the partition (for example, sales_year).

• Specify the ranges of values in the CREATE TABLE ... PARTITION BY RANGE clause.

• Specify a different tablespace (for best performance, place each on a separate disk) for each partition of the table. Note that separate tablespaces are not required, but this practice allows a partition of the table to be taken offline while maintaining access to the balance of the table.

Partitioned tables should usually be accompanied by a corresponding partitioned index, as follows:

• Use the LOCAL keyword in the CREATE INDEX command to tell Oracle to create a separate index for each partition of the indexed table.

• Use the GLOBAL keyword in the CREATE INDEX command to tell Oracle to create ...