Table partitioning enables a table to be divided into multiple self-contained sub-sections (partitions) that can be located in different physical areas. A row never spans a partition.

OpenEdge SQL supports the following kinds of partitions:

  • LIST — A LIST partition is defined by a single, specific column value for a specific column. A column's value in a table row must be equal to the value defined for a partition, if the row is to be assigned to that partition. NULL cannot be a list partition key value. A list partition allows only one value per partition definition. Using <= for a list partition results in a syntax error.
  • RANGE — A RANGE partition is defined by a logical range over a column's value for a specific column. A range has a lower bound and an upper bound value and must be explicitly defined by, at least, an upper bound value. The lower bound value of a range can span values from negative infinity to positive infinity.The upper bound must be a value that can be expressed as a regular SQL literal constant, such as 31-December-2025. The upper bound value must be greater than any expected value that applications use for a column. A range must not contain any gaps. For example, a date range of 2005-2015 must not exclude 2009. Values can be excluded from the beginning of all the ranges or from the end. Also, ranges cannot overlap. For example, if you are partitioning based on a date field and defining partitions for a year's worth of data, your partitions define ranges of January 1 through December 31, for each year.
  • Subpartitions — A partition can be further divided into subpartitions. Subpartitions are defined by multiple partition key columns. Each partition key column, after the first, defines a subpartition. Subpartitions have the same attributes of a partition like optional name, range or list types, etc. There can be up to 15 levels of subpartitioning. For a subpartition, all the leading subpartition levels must define list partitions. The last subpartition level can be either a RANGE partition or a LIST partition. The following types of subpartitions are supported:
    • LIST-RANGE — A LIST-RANGE partition first defines a partition by a list, and then further partitions the data based on a range.
    • LIST-LIST-RANGE — Only the last subpartition can be a RANGE partition.
  • Composite — A composite partition is a partition that contains the data for multiple partitions.

Information is provided for the SQL statement specific to partitioned tables.