Do functions like trim,upper,lower,etc.. on sqls instructions mess up with
the optimizer cost calculations or best path definition?

I'm having a serious performance problem in my production environment, and
I think the problem is:

in a sql like that:
select text1 from table1
where trim(text1='T111')
I got a sequential scan on the table.

but if I change the command to this:
select text1 from table1
where text1[1,4]='T111'
I got an index path scan.

The table I'm querying have more than a thousand rows and a bunch of
columns. This query is driving my server nuts!! Sometimes I get uptime
values over than 9.0 !! Often these values are about 0.9.

Is there any workaround (via parameters on env vars) that I can do?

PS: INFORMIX 7.31UD1 - Linux - Redhat 8.0
OPTCOMPIND=2 (onconfig)

Thanks in advance,
Julio Cesar
sending to informix-list

art s. kagel

Yes they do. Also, the engine cannot use an index (except in 9.xx where you
can have a functional index defined using the function you want to search
with) to compare to the results of a function.

I assume this should be: trim(text1) = 'T111'

Why not just do:

select text1
from table1
where text1 = 'T111';

?? If the text1 column ends in spaces, the engine will automagically generate
the correct trailing spaces to perform the match. The trim is unneccessary
just to remove trailing spaces for comparison. Is your question more general
and I'm missing it?


No, except to make the WHERE clause comparisons in such a way as to avoid
table scans when possible. Like the transform above or replacing:

AND substr( col1, 4 ) = 'AbCd'
with:
AND col1 matches 'AbCd*'
or:
AND col1[1,4] = 'AbCd' as you did above.


Art S. Kagel