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

CNAME
DESC

CNAME

Figure 13.4 When the
DBMS cannot read an
index backward.

CUST

z/OS introduced this facility in V8), browsing in both directions can be imple-
mented with a single index without a sort being required. Otherwise, two indexes
must be created to avoid a sort of the result rows, as in Figure 13.4.
A commonly used trick to avoid mirror indexes like this is to save the index
key values of the displayed screens in an auxiliary table. There is a limitation
in this, however, in as far as the user is not able to go backward beyond the
start point.
Note: Enabling the read backward facility may require an option to be
requested; in DB2 for LUW, for instance, ALLOW REVERSE SCANS must
be specified in the CREATE INDEX.

INDEX KEY TRUNCATION

Index key truncation (e.g., implemented in DB2 for z/OS) refers to the DBMS
storing only that part of an index key in the nonleaf pages that is necessary to
determine the page at the next lower level. As with the nonkey index columns
discussed above, it reduces the number of nonleaf pages.

FUNCTION-BASED INDEXES

Let us assume that column CNAME is stored in table CUST as mixed case ,
but value CNAME is moved in upper-case form into a SELECT host variable
(Fig. 13.5). A common solution would be to store CNAME twice in the CUST
table, once in mixed case, and a second time in upper case. The latter column
would be copied to the index. The two table columns would then have to be
synchronized, either with triggers or by a generated column.
With Oracle, a function-based index may be created:

CREATE INDEX .... ON CUST (UPPER (CNAME), CNO, CITY)

When a predicate contains the same function, as in SQL 13.3, the optimizer will
consider the function-based index. This eliminates the need for the redundant
column in table CUST.