At 03:42 PM 1/11/02 -0500, Wei Weng wrote:
>Can you index on a function?
>How exactly does that help performance of a query?
If a table "employee" contains a field "last" and you are not sure how the
last name might make use of capital letters, then to get all names starting
with 'MC', you might code a query like:select * from employee where last ILIKE 'MC%';
which does not use an index on "last" because we used ILIKE instead of LIKE.
In this case, we would create an index using "lower" function, like:create index employee_last on employee (
lower(last));
then write the query as:select * from employee where lower(last) LIKE 'mc%';
which does make use of the index (and a faster executing query).
Frank