Re: ONLINE V7.20 : Query Optimizer :BIG PROBLEM With LIKE 'AA%'

Incotec wrote: > > We have a big problem of performance with a select like this one: > > SELECT * FROM artid WHERE artid_reference LIKE 'AA%'> > The search is sequential (see with sqexplain.out)!!!!!! But there is a > index on the field > artid_reference. > The table have 200 000 records and the query can be most complex (join with > other table...). > > I have try value 0 or 2 for OPTCOMPIND. > I have use UPDATE STATISTICS ...... > > To test this just type under DBaccess: > create database test ; > create table x (y integer,x char(20)); > create index x on x(x); > set explain on; > select * from x where x like "aa%";> you will see the result. > Nota: if there is only one field in the table, it works correctly! > > Is there Anyone who can help me? > thanks > > -- > Georges SCHNEIDER > Incotec Inc. > > incotec@incotec.fr Georges, I think I have a solution for youre problem. I'm sorry but I'm not at my office right now so I can't test it, but I also had the same problem I think. Looking at your SQL statement I think if you try the following the index will be used. SELECT * FROM artid WHERE artid_reference[1,2] = "AA" Please let me now if it worked (mail me at my personal e-mail address) Regards, Rob Prop -- =============================================================== Rob Prop Tel. : +31-164-255300 Consultant Fax. : +31-164-246162 Informa Automatisering bv Email: rprop@concepts.nl Bergen op Zoom informa@pi.net The Netherlands Web : http://www.informa.nl ===============================================================