SQL/MX 2.x Reference Manual (H06.04+)

SQL/MX Clauses
HP NonStop SQL/MX Reference Manual—540440-003
7-7
Examples of Partitions
Decoupling of Clustering Key and Partitioning Key
Decoupling the clustering key from the partitioning key allows those keys to differ.
NonStop SQL/MX does not support full decoupling (that is, complete independence of
the keys), but does support partial decoupling in which the set of partitioning key
columns is allowed to be a subset of the clustering key columns. The composition of
the clustering key is described in the STORE BY clause. See the STORE BY Clause
on page 7-22. The partitioning key is made up of one of these:
•
The columns you specify in the PARTITION BY clause
•
The clustering key (omitting SYSKEY) if no PARTITION BY clause was specified
For creation of partitioned or range partitioned tables, the set of columns you specify
for the partitioning key can be identical to or a subset of the clustering key columns,
excluding the SYSKEY if present, and these columns can be specified in any order. A
decoupled partitioned or range partitioned index can be created.
Examples of Partitions
This example creates a table with three partitions that are on different physical
volumes and which have different extent sizes:
CREATE TABLE TIMBUKTOO
(ORDERNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
UNIT_PRICE NUMERIC (8,2) NO DEFAULT NOT NULL,
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY (ORDERNUM, PARTNUM) NOT DROPPABLE)
STORE BY PRIMARY KEY
LOCATION $DATA14.ZSDLKRIS.ZZZZ0000
ATTRIBUTE EXTENT (125000,125000) MAXEXTENTS 600
PARTITION
( ADD FIRST KEY (10000) LOCATION $DATA14 EXTENT 900
MAXEXTENTS 300,
ADD FIRST KEY (20000) LOCATION $DATA15 EXTENT (1024,2048)
MAXEXTENTS 600,
ADD FIRST KEY (30000) LOCATION $DATA16 MAXEXTENTS 599
EXTENT 66000);