Re[2]: ONLINE V7.20 : Query Optimizer :BIG PROBLEM With LIKE
Mail us all, please.
(Know that the informix optimizer is not able to decide to use indexes if an
"OR" exists in an SQL statement. It is purposely (knowingly) left out of the
optimizer algorithm. Sequential searching is invariably used, here, where index
use is obvious.)
Alan
______________________________ Reply Separator _________________________________
Subject: Re: ONLINE V7.20 : Query Optimizer :BIG PROBLEM With LIKE 'A
Author: Rob Prop <rprop@concepts.nl> at INTERNET
Date: 10/17/97 9:46 PM
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
===============================================================