Re: Cost Based Optimizer

This bug in the engines may be related to a matches problem with the optimizer: Bug: 11013 SELECT WITH MATCHES AND NO WILDCARDS SHOULD BE OPTIMIZED This bug is fixed in any 5.00* and later, and any 4.10.UE1 and later, product. Craig In article <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. I called tech support and all they could tell me was the cost based >optimizer does what it wants and I can't turn it off (Online 5.0). I hope this >is clear, and if it is does any one have an alternate soluition that making all >selects using MATCHES. > > >Regards, >Peter >.-------------------------. >| Peter Estabrook |___________________________________________ vvvvv >| User Technology Assoc. | Host : SEQUENT S2000/200 Dynix/ptx 1.4 / | - - | >| 2121 Crystal Drive #103 | Fax : (703) 486-7179 / | . . | >| Arlington, VA 22202 USA | Voice: (703) 486-7190 ( ( ^ ) >(-------------------------| Mail : estabroo@sea07s.navsea.navy.mil \\ \\`-'/ > (__________________________________________\\ \\_/ -- ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Craig Browning Regency Support Account Manager Informix Software, Inc.