Product Documentation

c-treeACE V11.0 Update Guide

Previous Topic

Next Topic

Partitioned Files in c-treeACE SQL

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.

TOCIndex