hi,
i have a sql where it uses function LENGTH like below
select * from tab1
where
...
and LENGTH(tab1.c1) = 0

i have a index on tab1.c1, but i doubt if it can make anyuse.

So should i have a index with LENGTH on the field or should i modify my sql.

Its role of the optimizer to use or dont use the index.

In some cases the sequential scan might be faster.

It also depends on the age of your statistics.

Set explain and look if the index is used or not.

Its not possible to create an index on build-in SQL functions, but you can create it on a stored procedure which returns the length.

You can force the optimizer to use the index but thats again this "I dont tell you too much for free"-situation.