Database Reference
In-Depth Information
key may be A, B while columns C and D are nonkey columns. Now, updates
to columns C and D will not move the index row. This may save one random
read from the disk drive (10 ms) per updated table row. Note, however, that if
columns A and B make the index unique, updating columns C or D would not
move the index row to another leaf page even if these were key columns.
Another important benefit is the reduction in the number of nonleaf pages:
only the key columns are stored in the levels above the leaf pages. The database
buffer pool size required to try to keep nonleaf pages in memory is reduced.
While nonkey index columns may make updates faster, SELECT calls that
refer to the nonkey columns in the WHERE clause or in the ORDER BY clause
may suffer. An example of this is shown in SQL 13.1 and Figure 13.2. As the
position of columns C and D in the index rows are now no longer guaranteed to
be in sequence, only two matching columns together with the necessity of a sort
are less satisfactory.
SQL 13.1
SELECT
D
FROM
TABLE
WHERE
A = :A
AND
B=:B
AND
C=:C
ORDER BY
D
The biggest benefit of nonkey index columns comes from the ability to add
index columns to the primary key index.
If the DBMS does not support nonkey index columns (or the enforcement of
uniqueness for a partial index key by means of a constraint, as described below),
MC = 3
SORT = N
MC = 2
SORT = Y
A,B,C,D
A,B, (C,D)
Figure 13.2 Nonkey index
columns may make SELECT
calls slower.
TABLE
TABLE
 
Search WWH ::




Custom Search