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

P

P

CNO G CNO

Trigger

CUST

GCUST

Figure 13.1 When the
DBMS does not support
index row suppression.

1,000,000 rows 1000 rows (CNO)

In fact, the feature is useful in indexes that are created to find exceptional
conditions, not because of the disk space savings but to eliminate unnecessary
index maintenance. A common circumvention is to build a small index-like table
maintained by a trigger, as shown in Figure 13.1.
Gold customers, one customer out of every thousand, have a value 1 in
column G, whereas ordinary customers have a value 0. If the DBMS supports
index row suppression, it would probably be used for index G. Otherwise, to
reduce the maintenance overhead of such an index, a table, GCUST, could be
created containing the primary keys of all the gold customers.
This index-like table, just as the index for customers with G = 1 that would
have been created if index row suppression had been allowed, is maintained only
in the following events:

ž A gold customer is added to table CUST.
ž A gold customer is removed from table CUST.
ž An ordinary customer is promoted to a gold customer.
ž A gold customer is downgraded to an ordinary customer.
With Oracle, index suppression can be implemented with NULL values, as
we saw in Chapter 12. In the example above, ordinary customers could have a
NULL value in column G instead of 0. Performance could be better than with
an index-like table (no join), and there is no need to create a trigger to maintain
the table GCUST. However, using NULL to represent a specific value is not a
recommended practice; it may lead to errors in the long run.

ADDITIONAL INDEX COLUMNS AFTER THE INDEX KEY

Index keys determine the position of an index row in the chain that connects all
the rows of an index. When the key value of an index row is updated, the DBMS
removes the index row and replaces it in the new position. In the worst case, the
index row is moved to another leaf page.
DB2 for LUW allows a split of the columns listed in the CREATE INDEX
into two groups: key columns and nonkey columns (option INCLUDE in CREATE
INDEX, as seen in Chapter 12). For instance, in index (A, B, C, D), the index