Create a Range-Partitioned Table

For best performance, each partition of a range-partitioned table resides on a separate segment.

This example creates a range-partitioned table called fictionsales; it has four partitions, one for each quarter of the year:

create table fictionsales
	(store_id int not null,
	order_num int not null,
	date datetime not null)
partition by range (date)
	(q1 values <= (“3/31/2004”) on seg1,
	q2 values <= (“6/30/2004”) on seg2,
	q3 values <= (“9/30/2004”) on seg3,
	q4 values <= (“12/31/2004”) on seg4)

The partitioning-key column is date. The q1 partition resides on seg1, and includes all rows with date values through 3/31/2004. The q2 partition resides on seg2, and includes all rows with date values of 4/1/2004 through 6/30/2004. q3 and q4 are partitioned similarly.

Attempting to insert date values later than “12/31/2004” causes an error, and the insert fails. In this way, the range conditions act as a check constraint on the table by limiting the rows that can be inserted into the table.

To make sure that all values, up to the maximum value for a datatype, are included, use the MAX keyword as the upper bound for the last-created partition. For example:

create table pb_fictionsales
	(store_id int not null,
	order_num int not null,
	date datetime not null)
partition by range (order_num)
	(low values <= (1000) on seg1,
	mid values <= (5000) on seg2,
	high values <= (MAX) on seg3)