NUMTOYMINTERVAL in archecker grammar file

Starting from 15.0.1.x, a new keyword, NUMTOYMINTERVAL, has been introduced into the archecker grammar file.

This keyword is used to define a time-based interval within the FRAGMENT BY RANGE clause when creating a table. It provides a straightforward way to specify an interval in years or months for automatic partition (fragment) creation.

This keyword simplifies the management of time-series data by allowing the database to automatically create new, time-based partitions as data grows, eliminating the need for manual intervention.

Syntax

The NUMTOYMINTERVAL keyword is used within the INTERVAL clause of a CREATE TABLE statement as follows:
Numeric to INTERVAL

1 
2.1 NUMTOYMINTERVAL
1 (
1 number
1 ,
2.1  ' YEAR '
2.1  ' MONTH '
1 )
Element Description Restrictions Syntax
number The number of years or months in the interval.

This can be an expression, including a column expression, that resolves (or can be cast) to one of the valid data types.

 Must be one of the following data types:
  • INT
  • BIGINT
  • SMALLINT
  • INT8
  • DECIMAL
  • REAL
  • FLOAT
  • SERIAL
  • SERIAL8
  • BIGSERIAL
 Literal number
MM Two digits specifying the number of months in the interval. A hyphen ( - ) must precede the first digit. Must be one of the following data types:
  • CHAR
  • NCHAR
  • VARCHAR
  • NVARCHAR
  • LVARCHAR
 Literal string
YY Two digits specifying the number of years in the interval. Must be one of the following data types:
  • CHAR
  • NCHAR
  • VARCHAR
  • NVARCHAR
  • LVARCHAR
 Literal string

Usage

The NUMTOYMINTERVAL keyword is supported exclusively within the INTERVAL clause of a CREATE TABLE statement that uses the FRAGMENT BY RANGE strategy.

When a row is inserted with a fragmentation key value that falls beyond the range of the highest existing partition, the database uses the interval defined by NUMTOYMINTERVAL to calculate the boundary for a new partition and creates it automatically. New partitions are created in the dbspaces listed in the STORE IN clause in a round-robin fashion.

Example

Consider a table designed to store order information, partitioned by the order_date. We want the database to automatically create a new partition for each new month of data:


CREATE TABLE orders (
    order_id INT, 
    cust_id INT,
    order_date DATE, 
    order_desc CHAR(1024)
)
FRAGMENT BY RANGE (order_date)
    -- This clause defines the automatic fragmentation interval
    INTERVAL (NUMTOYMINTERVAL(1, 'MONTH')) 
    -- New fragments will be stored in these dbspaces
    STORE IN (rootdbs, dbs1) 
    -- These are the initially defined partitions
    PARTITION p0 VALUES < DATE('01/01/2005') IN dbs1,
    PARTITION p1 VALUES < DATE('01/01/2006') IN dbs1,
    PARTITION p2 VALUES < DATE('01/01/2007') IN rootdbs,
    PARTITION p3 VALUES < DATE('01/01/2008') IN rootdbs;