Having covered virtual columns recently, I wanted to cover one additional
type of stored value, expression indexes. Unlike virtual
columns, which are computed on each access, expression indexes are stored in index files, allowing rapid access. Let’s look at some
examples, building on the customer table and fullname function created in my previous
blog entry:

CREATE INDEX i_customer_lastname ON customer (lastname);
 
CREATE INDEX i_customer_concat ON customer ((firstname || ' ' || lastname));
 
CREATE INDEX i_customer_fullname ON customer (fullname(customer));

Continue Reading »