Using a function on a column in where will cause it to be non-indexed

It is perfectly possible to write a scalar function on a field in the Select section of the SQL statement, but the following code:

Select Empno,lastname  from EMP where  Year (HireDate) = 2005

should be written as

Select Empno,lastname  from EMP where between "'  and "'

After overriding this statement as shown above, DB2 can choose to use an index on HireDate if one exists. However, if you use the year function, DB2 cannot use the index of that column.

Even though you can create an expression index using a function or an expression, you should rewrite the query as much as possible.

Using a function on a column in where will cause it to be non-indexed