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

UPPER(CNAME),
CNO,
CITY

Figure 13.5
Function-based index avoids
the need for a redundant
table column.

CNAME (mixed case)
No UPPER(CNAME)

CUST

SQL 13.3

SELECT UPPER (CNAME), CITY, CNO
FROM CUST
WHERE UPPER (CNAME) BETWEEN :hv1 AND :hv2
ORDER BY UPPER (CNAME)

Note: If the result must show CNAME in mixed case, column CNAME must
be added to the index to provide index-only access:

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

Oracle 9i supports expressions and functions (even user-written ones) in the CRE-
ATE INDEX, while SQL Server 2000 supports indexes on computed columns in
atable.

INDEX SKIP SCAN

Let us assume an index (CCTRY, BDATE, CNAME) on table CUST, but no index
starting with BDATE. Most optimizers would probably choose a full index scan
for SQL 13.4, but Oracle would be able to consider an index skip scan whereby
multiple slices, one slice per country as defined by CCTRY and BDATE, would
be read in parallel; skip scan skips the first index column. An index starting with
columns BDATE and CCTRY would then be superfluous if column CCTRY
had a low cardinality; consider one million customers in 20 different countries,
for instance.

SQL 13.4

SELECT CCTRY, CNAME
FROM CUST

WHERE BDATE = :BDATE FF = 0.01%