DBMS-Specific Indexing Options - Relational Database Index Design and the Optimizers - page 238

P U

CNO,
CNAME

CNO

Figure 13.3 When the DBMS
does not support nonkey
index columns.

CUST

columns should never be added to the primary key index because the DBMS
only ensures that the whole index key is unique. If column CNAME is added to
the primary key index CNO as a key column, it becomes possible to have two
rows in the table CUST having the same value in column CNO. To produce a fat
index for the SELECT shown in SQL 13.2 with a DBMS that does not support
nonkey index columns, an additional index (CNO, CNAME) may have to be
created, as shown in Figure 13.3.

SQL 13.2

SELECT CNAME

FROM CUST

WHERE CNO = :CNO

CONSTRAINTS TO ENFORCE UNIQUENESS

Oracle provides a different solution for the problem illustrated in Figure 13.3.
Index CNO is not needed because an index (CNO, CNAME) can be used to
enforce the uniqueness of the primary key CNO. This is done with a PRIMARY
KEY constraint. When a candidate key must be unique, UNIQUE constraints can
be used with an index that begins with the candidate key.
Note that index (CNO, CNAME) must be created without the keyword
UNIQUE in Oracle, although it is actually unique because it contains the pri-
mary key.

DBMS ABLE TO READ AN INDEX IN BOTH DIRECTIONS

If a DBMS can read an index backwards (which is becoming common; Oracle,
SQL Server, and DB2 for LUW have had this capability for some time; DB2 for