IBM Db2 11.1 Certification Guide

Expression-based indexes

Expression-based indexes are indexes whose keys are derived by applying a supported column expression. These indexes, like all others, are created using the CREATE INDEX statement. A typical opportunity when it comes to data, are mixed case searches, they are as follows:

-- Table Definition 
    
CREATE TABLE CUSTOMER (LASTNAME VARCHAR (30), FIRSTNAME VARCHAR (30), ADDRESS VARCHAR (2000)); 
 
-- SELECT Statement 
 
SELECT ADDRESS, LASTNAME|| ', ' ||FIRSTNAME FROM CUSTOMER  
   WHERE UPPER (LASTNAME) LIKE 'SMI%'; 

This SQL results in a table scan (TBSCAN), which can have poor results when run against a large table. Here, expression based indexes can help. We create an index on the LASTNAME, column with an UPPER function applied as ...