SQL/MP Installation and Management Guide

Creating a Database
HP NonStop SQL/MP Installation and Management Guide—523353-004
5-47
Defining Unique Indexes
Defining Unique Indexes
You can use the technique of defining index keys as UNIQUE to enhance the
performance of a SELECT statement that returns only one row. A unique index
requires that the value of the columns that make up the index key is unique in the
table. The index value is the value of columns together in the index and not the
individual values of the column.
If the table contains duplicate values in the specified columns, you cannot create a
unique index on those columns.
You should consider creating a unique index if you know that a column contains only
unique values and the column is used in SELECT queries that have a WHERE clause
of this format:
WHERE column = expression
If you create a unique index on the column, the system determines that only one value
can be returned, and the execution of the statement requests only one row.
If there is no unique index on the column, the system expects duplicate values in the
column and the executor must request a scan of values from the disk process. The
system then requests the disk process to validate that only one row satisfies the
WHERE clause of the statement.
This example shows a SELECT statement that uses the column SOC-SECURITY-
NUMBER, which has been defined as a unique index. The SQL executor requests only
one row from the disk process.
>> SELECT LAST_NAME, FIRST_NAME, EMPNUM, SOC-SECURITY-NUMBER
+> FROM EMPLOYEE
+> WHERE SOC-SECURITY-NUMBER = "534-90-1111";
Defining a unique index on columns is a technique that you might use to improve the
response only for the SELECT queries that use the column or columns indexed and
that return only one row. As with all indexes and keys, you must consider performance
issues for all DML operations before determining when to create an index.
For more information on how indexes can enhance performance, see Determining
When to Use Indexes on page 3-16, Optimizing Index Use on page 14-16, and
Maximizing Parallel Index Maintenance on page 14-17.
Creating Unique Indexes for Integrity Checking
One use of a unique index is to provide an integrity check between rows that is not
possible by using constraints. An index defined as unique prevents two or more rows
of the table from having the same values in the indexed columns. The system ensures
that rows inserted or updated satisfy the unique index requirement.