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

key of the underlying table are not included in the clustering key of the index, but are physically
included in the index file.
The clustering key for an index can contain columns with null values. However, two null values in
the same column are treated as equal values for purposes of unique constraint violation checking.
In calculating the length of an index key, consider the extra bytes added to the length of any column
that is not specified as NOT NULL NOT DROPPABLE. For more information, see the SQL/MX
Reference Manual.
Creating Indexes for Specific Situations
This subsection describes creating indexes for these situations:
• “Creating an Index for Frequently Used Columns” (page 40)
• “Avoiding Sort Operations” (page 40)
• “Creating Indexes for MIN and MAX Functions” (page 41)
• “Creating Indexes to Improve OR Operations” (page 41)
Creating an Index for Frequently Used Columns
If an application has many queries that reference a column in a table, creating an index on that
column might improve the performance of some of the queries. For example:
SELECT QTY_ORDERED, RETAIL_PRICE FROM INVNTRY
WHERE RETAIL_PRICE = 100 ;
If there is no index on RETAIL_PRICE, NonStop SQL/MX must scan the table and evaluate the
predicate (RETAIL_PRICE = 100) against each of the rows in the table.
An index on both columns (QTY_ORDERED and RETAIL_PRICE) would enable an index-only access.
That is, NonStop SQL/MX could retrieve all required data from the index and not have to read
the table. For example:
CREATE INDEX RPRICE
ON INVNTRY (RETAIL_PRICE, QTY_ORDERED) ;
For more information on how an index performance is affected by using the index as a predicate
for selecting rows or to satisfy ORDER BY, see “Evaluating the Benefits of a New Index” (page 156).
For another example, suppose that applications frequently access the EMPLOYEE table by employee
name (and the clustering key is EMPNUM). To speed the execution of these queries, create an
index of employee names based on the LAST_NAME and FIRST_NAME columns, as shown:
CREATE INDEX XEMPNAME
ON EMPLOYEE (LAST_NAME, FIRST_NAME) ;
Avoiding Sort Operations
Indexes can help the performance of queries that might require a sort operation, such as queries
that contain one or more of these keywords:
• DISTINCT
• GROUP BY
• ORDER BY
• UNION without ALL
Consider this query:
SELECT qty_ordered, retail_price FROM INVNTRY
ORDER BY QTY_ORDERED, RETAIL_PRICE ;
40 Understanding and Planning SQL/MX Tables