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-17
Determining When to Use Indexes
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 about the NOT NULL NOT DROPPABLE clause, 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 on page 4-17
•
Avoiding Sort Operations on page 4-17
•
Creating Indexes for MIN and MAX Functions on page 4-18
•
Creating Indexes to Improve OR Operations on page 4-18
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 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 on page 4-14.
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