Neat trick??? Seems to work well enough
Been using this for a while, thought people might be interested.
create table if not exists eric(
mystr char(30)
);load lots of rows;
create unique index if not exists eric_std on eric(mystr);
create function if not exists sp_niv(
mystr char(30)
)
returning char(30) with (not variant);return reverse(trim(mystr));
end function;
create index if not exists niv_rev on eric( sp_niv(mystr) );Testing with ~13M rows. You do need the directive for some reason once the table gets over a few thousand rows
forward.sql
select mystr from eric where mystr matches "*Cp4";reverse.sql
select {+INDEX(eric niv_rev)} mystr
from eric
where sp_niv(mystr) matches sp_niv("*Cp4")
real 1m27.805s
user 0m0.062s
sys 0m0.016s
real 0m0.910s
user 0m0.064s
sys 0m0.008s
------------------------------
Paul Watson
Oninit
www.oninit.com
Tel: +1 913 674 0360
Cell: +1 913 387 7529
Oninit® is a registered trademark of Oninit LLC
Failure is not as frightening as regret.
If you want to improve, be content to be thought foolish and stupid.
------------------------------
#Informix