| 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:
PARTITION BY RANGE statement, which is followed by the list of columns used to define the partition
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.
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(
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.