Partitioned Tables   «Prev  Next»
Lesson 4 Range partitioning
Objective Implement range partitioning.

Implement Table Range Partitioning in Oracle

The most basic type of partitioning for a table is called range partitioning.Range partitioning divides a table into partitions based on a range of values. You can use one or more columns to define the range specification for the partitions.

Syntax for range partitioned table

There are two parts for the syntax used to create a range partitioned table:
  1. The PARTITION BY RANGE statement, which is followed by the list of columns used to define the partition
  2. The VALUES LESS THAN statements, which define the values used as the upper bound for inclusion in the partition

Oracle automatically uses the upper bound of the next lower VALUES LESS THAN value as the lower bound of a partition.

Example

As an example, consider a table called YEARLY_SALES, which is partitioned based on quarters of the year. The statement used to define this table would be:

CREATE TABLE YEARLY_SALES(
 sales_month INTEGER,
 sales_amount NUMBER)
PARTITION BY RANGE (sales_month)
 (PARTITION sales1 VALUES LESS THAN 4,
 PARTITION sales2 VALUES LESS THAN 7,
 PARTITION sales2 VALUES LESS THAN 10,
 PARTITION sales2 VALUES LESS THAN 13);

You could assign each separate partition to its own tablespace at the end of the PARTITION clause, such as:
PARTITION sales2 VALUES LESS THAN 7 tablespace Q3

You can also define a partitioned table using Schema Manager. The next lesson demonstrates how to create indexes on a partitioned table.