Oracle Partition - Range partitioning (less than)

Card Puncher Data Processing


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


See Reference Range Partition

Oracle Range Partitions Syntax



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

Discover More
Card Puncher Data Processing
Oracle Partition - Index (Local, Global)

You can create two types of indexes on partitioned tables: Usable: The index is partitioned in exactly the same way as the base table. For instance, you can create a local index on the...
Card Puncher Data Processing
Oracle Partition - Partitioning Scheme

Oracle Database provides many types of partitioning options, including: Range: , , ,
Oracle Composite Range Partitions
Oracle Partition - Range Composite (With Sub-partitions)

A range partitioning scheme with Sub-partitions. composite_range_partitions (ie with subpartitions) The syntax...
Oracle Range Partitions Syntax
Oracle Partition - Range Interval Partitioning

A range partitioning where the database automatically creates partitions for a specified . At least one range partition using the PARTITION clause. The partitioning key can only be asingle column...
Oracle Subpartition By List
Oracle Partition - Range/List (Partition by Range, Sub-partition by List)

Range/List is a range composite scheme Partition by Range, Sub-partition by List For the range syntax, see range composite syntax. The subpartition list syntax is: subpartition_by_list...
Oracle Split Table Partition Syntax
Oracle Partition - Split

How to split actual: range partition range interval partition list partition in two. where: AT Clause: The AT clause applies only to range partitions. VALUES Clause: The VALUES clause...

Share this page:
Follow us:
Task Runner