Re: MATCHES problem

"ACE" <szepczynski@hotmail.com> wrote: :I've encountered a serious error of INFORMIX-ONLINE v. 7.1x, 7.2x :on following platforms: : DYNIX/ptx, SCO UNIX, AIX, DEC OSF UNIX, IRIX, Solaris and HP/UX. : Construction: :select tabid from systables where tabname matches 'sys*' : shows with EXPLAIN ON: SEQUENTIAL SCAN :select tabid from systables where tabname[1,3]='sys' : shows INDEX SCAN :(Indexes are not used even one use simple 'matches' phrase). :This error doesn't occur in versions 5.x of ONLINE. :Think about table with over 2,000,000 records. :On SEQUENT (Dynix/ptx) bug was corrected by setting OPTCOMPIND to 0. But :this fix doesn't work on another platforms. :Does anybody know how to fix this bug on another platforms? : ACE First I assume your select from systables is used only as an exsample, and the error also occurs on all other tables with an index on a character column. This doesn't occur on OnLine 7.10.UC1 on SCO 3.2.4.2 nor on OnLine 7.12.UC1 on SCO 5.0.0d I have OPTCOMPIND set to 0, so I don't know for sure if that would make a difference. I can't test that right now. Did you realy test the above on all those versions of Informix OnLine and operating systems? I have never seen this error in any version of Informix, OnLine or SE and I have worked with SE as long as it has been available (10 years or so, is it?) and OnLine since version 5.x and used many versions since that. We have allways used indexes on character columns and relied on matches "xxx*" to use that index. It has allways worked very well. If this bug is real it's prety devastating. Can you tell us something more? Exact versions of OnLine and OS would be fine. If I ever had this bug it would be a priority one case with Informix support. Our applications wouldn't run at all without this functionality. By the way: One idea is that it isn't a bug at all. May be the optimiser simply decided it wasn't a good idea to use the index on this realatively small table. That a sequencial scan would be faster in this case. You might want to test this on a larger table as well (at least several thousand rows, assuming you don't have this number of tables in your database). Nils.Myklebust@idg.no NM Data AS, P.O.Box 9090 Gronland, N-0133 Oslo, Norway My opinions are those of my company The Informix FAQ is at http://www.iiug.org