SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-43
Creating an Index
Guidelines
When defining indexes, consider these general guidelines:
•
When you create an index, you specify the column or columns that make up the
key. The key is classified into one of three levels, depending on the data types of
the columns that make up the key. The key level affects performance as described
under Key Levels on page 3-4.
•
When you create an index, the index inherits the type of partition array associated
with the base table. To take advantage of the larger number of indexes and index
partitions available with versions 320 and later of SQL/MP software, specify an
extended partition array for the base table.
•
Define key columns with NO DEFAULT NOT NULL and define nonkey columns
with SYSTEM DEFAULT NOT NULL (if the columns do not allow null values) to
save two bytes of storage space for each column and avoid complex null logic.
•
Define index columns so that their order reflects frequency of access and relative
importance of queries competing for the index (see Figure 5-2 on page 5-44):
°
Specify columns to be accessed with equality predicates as leftmost columns.
°
Follow these columns by columns with inequality predicates (predicates that
specify a range).
°
If there is more than one column that will be accessed with an inequality
condition, consider ordering the columns by decreasing selectivity (as
described in the SQL/MP Query Guide). These columns become positioning
columns because they are used to position within the index and mark the
range of qualifying rows.
°
Follow the index columns with nonpositioning columns that will have predicates
specified on them and columns that will be selected and have not been
included already.
Note. SQL tables and indexes with many partitions (typically around 400) might cause
SQLCAT, SQLUTIL, or AUDSERV processes to incur file-system error 31 or 34 or cause the
PARTNS catalog table and its associated index, IXPART01, to become full. For more
information about this situation, see Creating Table Partitions
on page 5-32.