SQL/MX Comparison Guide for SQL/MP Users

Data Definition Language (DDL) Differences
HP NonStop SQL/MX Comparison Guide for SQL/MP Users—523735-003
6-5
Indexes
Indexes
You can create indexes for SQL/MX files, with these differences from NonStop
SQL/MP:
•
Index table attribute
NonStop SQL/MX allows you to set these index table attributes: ALLOCATE,
AUDITCOMPRESS, BLOCKSIZE, CLEARONPURGE, EXTENT, and
MAXEXTENTS.
NonStop SQL/MP allows you to set these index table attributes: ALLOCATE,
AUDITCOMPRESS, BLOCKSIZE, BUFFERED, CLEARONPURGE,
DCOMPRESS, EXTENT, FORMAT, ICOMPRESS, ISLACK, LOCKLENGTH,
MAXEXTENTS, NOPURGEUNTIL, SERIALWRITES, SLACK,TABLECODE, and
VERIFIEDWRITES.
•
Index table access
NonStop SQL/MX does not support WITH SHARED ACCESS.
•
Partition changes
NonStop SQL/MX allows you to define secondary partitions as range or hash
partitions.
•
Populating indexes
If you are creating an index on a large SQL/MX table that is already populated, you
should use the NO POPULATE option, and then run the POPULATE INDEX utility
to load the index. Because CREATE INDEX executes in a single TMF transaction,
it could experience TMF limitations such as a transaction timeout if a large amount
of data is to be moved. See the SQL/MX Installation and Management Guide for
information about creating and populating indexes and the SQL/MX Reference
Manual for details about the CREATE INDEX statement and the POPULATE
INDEX utility.
•
PARALLEL EXECUTION ON | OFF option
NonStop SQL/MX uses the optimizer to determine whether to perform parallel
index loading. It is not a specifiable attribute.
•
INVALIDATE | NO INVALIDATE option
SQL./MX does not allow you to control whether or not changes to indexes cause
similarity checks and possible recompiles.
Partitions
NonStop SQL/MX allows you to locate as many partitions as you want on the same
disk. Like SQL/MX tables, partitions use ANSI logical names. You can specify
Guardian names for a partition.