SQL/MX 2.x Installation and Management Guide (G06.24+, H06.03+)

Understanding and Planning SQL/MX Tables
HP NonStop SQL/MX Installation and Management Guide—523723-004
4-13
Determining When to Use Indexes
When compiling a statement, NonStop SQL/MX selects the query execution plan for a
statement by choosing the best access path to the data. If an index exists, NonStop
SQL/MX evaluates using the index. Indexes give the optimizer more possible access
options.
Each index is assigned a name and is physically stored in a separate key-sequenced
file. Index files are not tables, and they cannot be queried directly through NonStop
SQL/MX. They are only a tool for providing faster access to tables.
Precreating Indexes or Managing Constraint-Created
Indexes
NonStop SQL/MX automatically creates an index for you whenever you create a
UNIQUE constraint, droppable PRIMARY KEY constraint, or REFERENTIAL
INTEGRITY constraint. The constraint, in turn, uses the created index to validate the
constraint’s operation. An index that is created when you create a constraint uses the
table’s primary partition’s attributes with respect to size and location. The index is
created unpartitioned.
To specify your own attributes for an index:
•
If you have not yet created the constraint that requires a supporting index, first
create the index using the desired attributes, then create the constraint.
•
If you have already created the constraint and want to change the attributes of the
supporting index, use ALTER TABLE to change its MAX EXTENTS and use
MODIFY to change its location or to add one or more partitions.
Creating Indexes With the NO POPULATE Option
If you need to create an index on a table that is already populated, 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 (for example, transaction timeout) if a large amount of data must be
moved. For information about the POPULATE INDEX utility, see the SQL/MX
Reference Manual.
Performance Benefits of Indexes
Indexes can improve performance by eliminating the need for the disk process to
access the underlying table. If the query can be satisfied by the columns contained in
the index, the underlying table is not accessed. By using only the index, you reduce I/O
to the table.
For example, consider this query in which ATABLE has a unique index named
AINDEX, which contains columns A and B, in that order, from ATABLE:
SELECT A,B FROM ATABLE;