Re: MATCHES problem

There was once a major problem with MATCHES used in databases with NCHAR or NVARCHAR variables, i.e. native language character types. I don't know if this is still a problem (we decided at the time to not use NCHAR and haven't tested it since), and I don't know if the poster's problem is related to this. As Nils points out, to not be able to use indices with matches would render Informix quite useless. Nils Myklebust wrote: > > "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 -- ---------------------------------------------------------------------- John H. Frantz Power-4gl: Extending Informix-4gl frantz@centrum.is http://www.rl.is/~john/pow4gl.html