|
|
|
Partitioned File Key Rule Using Conditional Expressions
Conditional expressions make it easy to build a partition rule. A partition rule is a conditional expression ultimately evaluating to a number. That number associates data with a specific partition within the file.
FairCom DB expression parsing can now evaluate an expression into a numeric representation to be used for the partition rule. Many built-in conditional expression functions exist for flexible rule generation. Time and date based functions, numeric functions, and string manipulation functions are all available. FairCom DB expression syntax can even reference complex functions via an external shared library (DLL) in calculating partition numbers.
If the table has an embedded DODA resource and a conditional expression references schema segments, an expression can refer to index key fields explicitly by DODA name. Together with field names, complex expression rules can be crafted.
Prior to FairCom DB V11, partitioned files required these rules to be hard-coded at compile time (specified in ctpart.c) and did not allow run-time flexibility in adapting rules for unique deployed environments.
By combining existing FairCom DB advanced support for conditional expressions with our high performing partitioned files, applications can now create exact expression rules directly when creating partitioned files. In addition, existing partitioned files can have their partition rule changed and subsequently rebuilt based on a new rule logic allowing applications to grow and adapt with their customer needs.
Partitioned files are supported directly from the FairCom DB ISAM API, c-treeDB and FairCom DB SQL.
Create with SQL
In FairCom DB 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 FairCom DB 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.
Create with c-treeDB
Partitioned file support is extended to the c-treeDB API. While creating a file it is possible to call ctdbSetTablePartitionIndexNbr to set the partition index, ctdbSetTablePartitionNumberBits to set the number of bits reserved for partition numbers, and ctdbSetTablePartitionRule to set the partition rule.
On existing tables, after calling the above function you then call ctdbAlterTable forcing an CTDB_ALTER_FULL action.
ctdbSetTablePartitionRule
This c-treeDB function sets partition rules:
ctdbEXPORT CTDBRET ctdbDECL ctdbSetTablePartitionRule(CTHANDLE Handle, pTEXT expr);
The partition rule uses standard c-tree expression syntax.
Create with ISAM
Partitioned files are created with standard c-tree APIs. A partitioning conditional expression is defined with the PTADMIN Partition Administration API call and the ptADMINrule parameter. Specify an extended (Xtd8) ctPARTAUTO mode in the x8mode parameter of an extended file creation block. While partitioned files require an extended (Xtd8) header they do not have to be HUGE files unless the logical file size will exceed the 2GB/4GB limit.
A partition key is set when the file is created using the prtkey parameter of the extended file creation block. The default is 0 (the first key associated with the data file). Set this value to the relative key number for the desired index if the default is not appropriate for your application.
A partition file name is the base data file name with a 3-digit raw partition number as the file extension. Only automatic naming is available in this mode.
Note: Alternative file naming is possible with custom modifications to the ctpart.c module and recompiling your FairCom Database Engine.
By default, 16 bits of the 64-bit record offset reference the raw partition number, allowing each partitioned file to support up to 65,535 member files over its lifetime, thus also somewhat limiting overall file size (i.e., the more bits used for partition numbering, the smaller the overall size of the file can be). The maximum value is 32-bits (4,294,967,295 member files). This numbering vs. size tradeoff is set at file create time using the callparm parameter of the extended file creation block (Xtd8), where a value of 0 defaults to 16-bits. Values less than 4-bits default to 4-bits (maximum 15 member files).
Note: Default partition naming, partition rules, and maximum number of partitions are used by default when not defined by the Xtd8 extended parameter block and the PTADMIN API call.
