1.0

[ EXPIRE { TABLE | ENTRY } WITH { IDLETIME value | TIMETOLIVE value}
ACTION { DESTROY | INVALIDATE } ]*
[ PERSISTENT ] [ 'disk-store-name' ] [ ASYNCHRONOUS | SYNCHRONOUS ]
Note: This topic focuses only on the partitioning_clause. The CREATE TABLE reference page
describes all of the options in more detail.
The partitioning_clause controls the location and distribution of data in server groups. Using server
groups and colocation is important for optimizing queries, and it is essential for cross-table joins. This version
of SQLFire does not support cross-table joins for non-colocated data, so you must choose the partitioning clause
carefully to enable the joins required by your application.
The partitioning clause can specify column partitioning, range partitioning, list partitioning, or expression
partitioning:
{
{
PARTITION BY { PRIMARY KEY | COLUMN ( column-name [ , column-name ]* )
}
|
PARTITION BY RANGE ( column-name )
(
VALUES BETWEEN value AND value
[ , VALUES BETWEEN value AND value ]*
)
|
PARTITION BY LIST ( column-name )
(
VALUES ( value [ , value ]* )
[ , VALUES ( value [ , value ]* ) ]*
)
}
[ COLOCATE WITH ( table-name [ , table-name ] * ) ]
}
[ REDUNDANCY integer-constant ]
[ MAXPARTSIZE integer-constant ]
[ BUCKETS integer-constant ]
Note: If the table has no primary key, then SQLFire generates a unique row ID that is uses for partitioning
the data.
SQLFire supports the partitioning strategies described below.
DescriptionPartitioning strategy
The PARTITION BY COLUMN clause defines a set of column names to use as the basis for
partitioning. As a short-cut, you can use PARTITION BY PRIMARY KEY to refer to the table's
Column partitioning
primary key column(s). SQLFire uses an internal hash function that typically uses the hashCode()
method of the underlying Java type for the specified column. For multiple columns, the internal hash
function uses the serialized bytes of the specified columns to compute the hash.
The PARTITION BY RANGE clause specifies the ranges of a field that should be colocated.
This ensures the locality of data for range queries and for cross-table joins. The lower limit of the range
Range partitioning
is inclusive and the upper limit is exclusive. It is not necessary for the ranges to cover the whole spectrum
of possible values for the field. Values that are not covered by the range are automatically partitioned
in the server group, but with no guarantee of locality for those values.
55
Partitioning Tables