SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-44
Creating an Index
•
Arrange columns to minimize unpacking or maximize bulk move operations,
depending on how the columns are used:
°
To avoid unpacking operations, arrange columns to end at word boundaries.
°
To optimize bulk moves, place columns in groups that will be selected together.
•
An index column can refer to a different collation from the collation used by the
corresponding base table column, provided the shifting rules for both collations are
the same.
•
Collation of single-byte character data is performed in the order represented by the
ordinal positions of the characters in the ASCII set. Alternatively, you can specify a
different collating sequence for single-byte character columns by creating a
collation object and associating the collation with the character data. If you do
associate a collation with the column, the character set associated with the
collation must be the same as the character set associated with the column.
•
Indexes you create before a table is loaded are loaded automatically as the table is
loaded.
•
You can load partitioned indexes in parallel. For more information, see Specifying
Parallel Loading of Index Partitions on page 5-49.
•
If you are loading an index on a large table, you might need to set the
=_SORT_DEFAULTS DEFINE to enable FastSort to use alternate swap files or
scratch file volumes. The SQL/MP Reference Manual describes this DEFINE.
•
Indexes can be updated in parallel by the disk process after the table has been
updated. To take full advantage of parallel updating, you should create a table’s
indexes on separate disk volumes, with each disk volume configured for a separate
processor. The performance effects of parallel updates are discussed under
Maximizing Parallel Index Maintenance
on page 14-17.
•
If you are creating an index on an existing table, follow the CREATE INDEX
statement with an UPDATE STATISTICS statement to update the statistics in the
catalog for the table.
Figure 5-2. Ordering Columns Within an Index
. . .
VST006.vsd
Columns with equality
predicates, in order of
descending selectivity,
from frequently used or
otherwise important
queries
Column with
range (inequality)
predicate with
lowest selectivity
Non-
positioning
key columns
Columns for
index-only
access
Primary key
columns (implicitly
added and maintained
by NonStop SQL/MP)