SQL/MX 3.2.1 Management Manual (H06.26+, J06.15+)

• 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.
• To influence the optimizer’s choice of index, use the CONTROL QUERY SHAPE statement. For
more information, 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;
Creating and Managing Partitions for SQL/MX Indexes
Like SQL/MX tables, indexes can be range partitioned, decoupled range partitioned, and hash
partitioned.
Use the MODIFY INDEX utility to manage range-partitioned and hash-partitioned indexes. For more
information, see the information on using MODIFY to manage table and index partitions in
“Reorganizing SQL/MX Tables and Maintaining Data” (page 178).
An important consideration when you partition indexes is whether to co-locate the index partitions
with the base table partitions. For more information on co-location, see the information on using
co-located indexes in “Enhancing SQL/MX Database Performance” (page 293).
Examples for Creating a Range-Partitioned Index
This example creates a range-partitioned 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
PARTITION (ADD FIRST KEY 'E' LOCATION $data1,
ADD FIRST KEY 'J' LOCATION $data2,
ADD FIRST KEY 'O' LOCATION $data3,
ADD FIRST KEY 'T' LOCATION $data3);
Examples for Creating a Hash-Partitioned Index
This example creates a hash-partitioned index on two columns of a table:
CREATE INDEX xempname
ON persnl.employee (last_name, first_name)
HASH PARTITION;
This example creates and hash-partitions a unique index (one that could be used to support a
UNIQUE, PRIMARY KEY, or referential constraint) on a table. The partitioning key consists of the
index columns LAST_NAME and EMPNUM:
98 Creating an SQL/MX Database