|
|
|
In c-treeACE SQL, a table is partitioned when a partition index is created. To create a partition index with conditional expressions for partitioning, use the following syntax:
CTREATE INDEX .... STORAGE_ATTRIBUTES 'partition=<expression>'
To create a partition index forcing hard-coded rules (pre-V11), the following is supported:
CTREATE INDEX .... STORAGE_ATTRIBUTES 'partition'
To change a partition rule on an existing partitioned file, call ALTER INDEX with your new rule in the STORAGE_ATTRIBUTES clause.
Example
This script demonstrates partition file rules in SQL. It creates a table, prtest, with an index on the integer field f7. The storage_attributes clause define a partition rule in which each record is stored in a partition number equal to the value of field f7 plus 1:
create table prtest (f1 integer, f2 char(50), f3 char (50), f4 char(50), f5 timestamp, f6 varchar(50), f7 integer, f8 time);
create index pridx on prtest (f7) storage_attributes 'partition=f7+1';
With a rich assortment of conditional expression functions available, much more complex rules can be created.
For example, a table containing a field "invoice_date" and requiring monthly partitions can be created with this simple expression:
month(invoice_date)
For syntax details, refer to Conditional Expression Parser topics in the c-treeACE Programmer's Reference and Function Reference Guide.
Example
Using functions to convert Unix time_t fields to c-tree Date and Time types (TIMET2CTDATE) in a partitioned file expression to partition into months since Jan, 2010:
CREATE TABLE unixtest (name CHAR(10), u_date INTEGER)
or
CREATE TABLE unixtest (name CHAR(10), u_date BIGINT)
CREATE INDEX unixtest_date_idx ON unixtest (u_date) STORAGE_ATTRIBUTES 'partition=( ( YEAR( TIMET2CTDATE( u_date) ) -2010) * 12) + MONTH ( TIMET2CTDATE(u_date))'
Date String Unix Date Partition created
Mon, 23 Feb 2015 11:01:32 GMT 1424689292 62
Sat, 23 Jan 2016 11:01:32 GMT 1453546892 73
Tue, 23 Feb 2016 11:01:32 GMT 1456225292 74
Wed, 23 Mar 2016 11:01:32 GMT 1458730892 75
For syntax details, for the TIMET2* functions, see C Language Equivalents.
