Re: Cost Based Optimizer

In <25kfoiINN8t0@emory.mathcs.emory.edu> estabroo@sea07s.navsea.navy.mil (Peter Estabrook) writes: >I've come across an oddity with the Infromix cost base optimizer. It seems if >you have a query that contains a matches on one column and an = on another it >first uses the key on the column with the = and then it does a sequential search >on the column with the matches. >Example. SELECT * FROM table WHERE col1 MATCHES "string*" AND col2 = "string2" >In our database the column with the matches returns 9 rows. The column with the >= returns 32,000 rows. If I use a matches on both columns the optimizer will >use the first index it comes across and returns in 1 sec, otherwise it takes >over 7 minutes to go sequentialy through the 32,000 rows findind the string >match. You might do some timing tests, but I suspect that if there are no wildcards in string2, then 'matches' is just as fast as '=', so you could leave it with both 'matches'. I did some crude tests on this as I ran into the same problem, and have been using the double matches since. -- Andrew Burt aburt@du.edu "But if he was dying he wouldn't bother to carve "Aaaaargh", he'd just say it."