Creating Partitioned Tables

You create a partitioned table on a set of servers identified by named server groups (or on the default server group if you do not specify a named server group). Clauses in the CREATE TABLE statement determine how table data is partitioned, colocated, and replicated across the server group.

This topic focuses on the partitioning_clause in CREATE TABLE. 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 ]* ) ]*
   )
   |
   PARTITION BY ( expression )
  }
 [ COLOCATE WITH ( table-name [ , table-name ] * ) ]
}
[ REDUNDANCY integer-constant ]
[ BUCKETS integer-constant ]
[ MAXPARTSIZE integer-constant ]
[ RECOVERYDELAY 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.

Partitioning strategy

Description

Column partitioning

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 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.

Range partitioning

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 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.

List partitioning

The PARTITION BY LIST clause specifies the set of values of a field that should be colocated to optimize queries and to support cross-table joins. It is not necessary to list all of the possible values for the field. Any the values that are not part of the list are automatically partitioned in the server group, but with no guarantee of locality for those values.

Expression partitioning

The PARTITION BY ( expression ) clause that includes an expression is a type of hash partitioning that uses the expression to specify the value on which to hash. The expression must only reference field names from the table. This allows rows to be colocated based on a function of their values.