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

Creating an SQL/MX Database
HP NonStop SQL/MX Installation and Management Guide—523723-004
7-33
Partitioning Indexes
•
Indexes can be updated in parallel by the disk process while the table is being
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 in
Section 16, Enhancing SQLMX Database Performance.
•
You should perform an UPDATE STATISTICS on the table before populating the
index using, at a minimum, the ON EVERY KEY option.
•
Index creation can be a long operation, depending on the size of the table and the
load on the system. NonStop SQL/MX supports only default locking, which requires
a shared table lock on the underlying table. The shared table lock ensures that no
users can modify rows during the creation of the index. This lock can prohibit
access to the table by other users.
•
All SQL/MX indexes that are not created with the NO POPULATE option are
audited. Immediately after creating an audited index, make a TMF online dump of
the index to prepare for possible file recovery, which might be faster than rebuilding
the index.
•
If an index is added or removed, query execution plans can be rendered
inoperable.The SQL/MX executor will perform a similarity check on such a plan
and, if necessary, automatically recompile it. To avoid this, you can explicitly SQL
compile the program. For specific guidelines, see the information on similarity
checks and automatic recompilation in the SQL/MX Programming Manual for C
and COBOL and the SQL/MX Programming Manual for Java.
•
To influence the optimizer’s choice of index, use the CONTROL QUERY SHAPE
statement. For more information about the use this statement, see the SQL/MX
Query Guide and the SQL/MX Reference Manual.
Examples—Creating an Index for SQL/MX Tables
This example creates an index on two columns of a table:
CREATE INDEX xempname
ON persnl.employee (last_name, first_name);
This example creates and partitions a unique index (one that could be used to support
a UNIQUE, PRIMARY KEY, or referential constraint) on a table:
CREATE UNIQUE INDEX XEMP
ON persnl.employee (LAST_NAME, EMPNUM)
LOCATION $data1
ATTRIBUTE NO AUDITCOMPRESS;
Partitioning Indexes
Like SQL/MX tables, indexes can be range partitioned, decoupled range partitioned,
and hash partitioned.