Oracle Partition - Range partitioning (less than)

About

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

Syntax

See Reference Range Partition

_

where:

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
)

The values of the predicate must be between parentheses


Powered by ComboStrap