1.0

VALUES BETWEEN 31 AND 50
);
Partition Based on a List
When you use the PARTITION BY LIST clause, specify a column name and one or more lists of column values
to use for partitioning. The following example partitions the table based on three different lists of values for the
"Status" 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 LIST ( Status )
(
VALUES ( 'pending', 'returned' ),
VALUES ( 'shipped', 'received' ),
VALUES ( 'hold' )
);
Partition Based on an Expression
Expression partitioning partitions a table by evaluating a SQL expression that you supply. For example, the
following statement partitions the table based on the month of the OrderDate column, using the MONTH function
as the SQL expression:
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 ( MONTH( OrderDate ) );
Colocating Related Rows from Multiple Tables
The COLOCATE WITH clause specifies the tables with which the partitioned table must be colocated.
Note: Tables that are referenced in the COLOCATE WITH clause must exist at the time you create the
partitioned table.
When two tables are partitioned on columns and colocated, it forces partitions having the same values for those
columns in both tables to be located on the same SQLFire member. For example, with range or list partitioning,
any rows that satisfy the range or list are colocated on the same member for all the colocated tables.
When you specify the COLOCATE WITH clause, you must use the PARTITION BY clause to specify partition
columns in the target tables in the same order using the same partitioning strategy (for example, with identical
57
Partitioning Tables