SQL/MX 2.x Database and Application Migration Guide (G06.23+, H06.04+, J06.03+)

Converting an SQL/MP Database to SQL/MX Tables
HP NonStop SQL/MX Database and Application Migration Guide—540435-005
11-12
Indexes
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 an SQL/MP index,
each row in an SQL/MX index contains the columns specified in the CREATE INDEX
statement and the clustering (primary) key of the underlying table. However, an
SQL/MX index does not contain a keytag column. Table 11-3
shows other difference
between SQL/MP and SQL/MX indexes.
In SQL/MX indexes, you can set some of the SQL/MP index attributes, as Table 11-4
shows.
Table 11-3. Differences Between SQL/MP and SQL/MX Indexes
Index Characteristic NonStop SQL/MP NonStop SQL/MX
Index table access Supports WITH SHARED
ACCESS
Does not support WITH
SHARED ACCESS
Partition changes Supports only range
partitioning
Supports range, decoupled
range, and hash
partitioning
Populating indexes Populates the index with
data from the table when
the CREATE INDEX
statement executes
Provides the NO
POPULATE option, which
allows you to defer
populating the index. You
can later populate the
index by using the
POPULATE INDEX utility.
Parallel execution Allows you to specify
PARALLEL EXECUTION
ON or OFF for parallel
index loading
The system determines
whether to perform parallel
index loading.
Invalidate option Allows you to control
whether changes to
indexes cause similarity
checks and automatic
recompilation with the
INVALIDATE option
Does not allow you to
control similarity checks of
indexes with an
INVALIDATE option
Index table attribute See Table 11-4
on
page 11-13.
See Table 11-4 on
page 11-13.