Oracle Partition - Range partitioning (less than)

About

The table is partitioned by ranges of values from one (or more) columns.

Articles Related

Syntax

See Reference Range Partition

where:

• column: an ordered list of column
• MAXVALUE specifies a maximum value that will always sort higher than any other value, including null.
• for interval range partitioning. See Oracle Partition - Range Interval Partitioning

Example

For example, in a table of customer transactions called TRANS, you can range-partition it by:

• using the TRANS_DT (transaction date) column as the partition key
• so that:
  • a first partition holds records in which the TRANS_DT value is between January 1 and March 31, 2005;
  • the second partition holds records in which the TRANS_DT value is between April 1 and June 30; and so on.

The listing below creates this table:

-- Script for creating the TRANS table with range partitions
create table trans (
	trans_id	number,
	trans_dt	date,
	product_code	number,
	store_id	number,
	trans_amount	number(12,2)
)
partition by range (trans_dt)
(
  partition "y05q1" values less than (  to_date('04/01/2005','mm/dd/yyyy')  ) tablespace y05q1,
  partition "y05q2" values less than (  to_date('07/01/2005','mm/dd/yyyy')  ) tablespace y05q2,
  partition "y05q3" values less than (  to_date('10/01/2005','mm/dd/yyyy')  ) tablespace y05q3,
  partition "y05q4" values less than (  to_date('01/01/2006','mm/dd/yyyy')  ) tablespace y05q4,
  partition "pmax" values less than (maxvalue) tablespace users
)