SQL/MP to SQL/MX Database and Application Migration Guide

Converting SQL/MP Tables to SQL/MX Tables
HP NonStop SQL/MP to NonStop SQL/MX Database and Application Migration Guide—666211-001
5-11
Partitions
unique or primary key) in the referenced table. A referential integrity constraint ensures
that the foreign key contains only values that match those in a column or set of
columns in the referenced table. A unique constraint ensures that a column or set of
columns contain only one occurrence of a non-null value or set of values.
For more information about SQL/MX constraints, see the
SQL/MX Reference Manual.
Partitions
In NonStop SQL/MX, use the PARTITION clause of the SQL/MX CREATE TABLE (or
CREATE INDEX) statement to create partitions of a SQL/MX table (or index). Unlike
NonStop SQL/MP, which allows only one partition per disk volume, you can locate as
many SQL/MX partitions as you want on the same disk volume.
NonStop SQL/MX supports decoupled range partitioning.
Decoupled means that the
partitioning key need not be the same as the clustering key. The partitioning key can
be a subset of the clustering key columns, and those columns can be specified in a
different order. Decoupled range partitioning is useful for co-locating range-partitioned
index data on the same disk volume as the underlying table rows.
In addition to range partitioning, NonStop SQL/MX supports hash partitioning of
SQL/MX tables. Hash partitions are better for larger queries that run mostly in parallel
and access all partitions. To generate hash partitions, NonStop SQL/MX uses a hash
function on the values of the partitioning key and assigns each record to a partition
based on the result. For more information about hash partitioning, see the
SQL/MX
Reference Manual.
Partition management differs in NonStop SQL/MP and NonStop SQL/MX. In NonStop
SQL/MP, you use the SQL/MP ALTER TABLE or ALTER INDEX statement to manage
partitions. In NonStop SQL/MX, you use the SQL/MX MODIFY utility to manage
partitions. For more information about managing partitions in NonStop SQL/MX, see
the
SQL/MX Installation and Management Guide.
Indexes
In NonStop SQL/MX, you can create indexes for SQL/MX files. Like a SQL/MP index,
each row in a SQL/MX index contains the columns specified in the CREATE INDEX
statement and the clustering (primary) key of the underlying table. However, a SQL/MX
index does not contain a keytag column. Table 5-3
shows the differences between
SQL/MP and SQL/MX indexes.