1.0

DescriptionPartitioning strategy
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
List partitioning
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.
The PARTITION BY 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.
Expression partitioning
Partitioning Examples
You can partition tables by, for example, column (such as customer name), expression, priority ranges, and
status.
Partition Based on Columns
This statement creates a table that is partitioned by the "CustomerName" column. All rows with the same
CustomerName are guaranteed to be colocated in the same process space. Here, the SERVER GROUPS clause
determines the peers and servers that host data for the partitioned table. A server group is a subset of all the peers
and servers that host data in the distributed system.
CREATE TABLE Orders
(
OrderId INT NOT NULL,
ItemId INT,
NumItems INT,
CustomerName VARCHAR(100),
OrderDate DATE,
Priority INT,
Status CHAR(10),
CONSTRAINT Pk_Orders PRIMARY KEY (OrderId)
)
PARTITION BY COLUMN ( CustomerName )
SERVER GROUPS ( OrdersDBServers);
Partition Based on Ranges
When you use the PARTITION BY RANGE clause, specify a column with multiple ranges of values to use for
partitioning. The following example specifies partitioning based on three ranges of values for the "Priority"
column:
CREATE TABLE Orders
(
OrderId INT NOT NULL,
ItemId INT,
NumItems INT,
CustomerName VARCHAR(100),
OrderDate DATE,
Priority INT,
Status CHAR(10),
CONSTRAINT Pk_Orders PRIMARY KEY (OrderId)
)
PARTITION BY RANGE ( Priority )
(
VALUES BETWEEN 1 AND 11,
VALUES BETWEEN 11 AND 31,
vFabric SQLFire User's Guide56
Managing Your Data in vFabric SQLFire