Re: Cost Based Optimizer

In <1993Sep22.074111.11287@informix.com> craigbr@informix.com (Craig Browning) writes: >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 This seems to me to be a different issue, altogether. Peter's problem doesn't sound like a bug at all, but is the behaviour I would expect: >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. This is to be expected. OnLine uses a Selectivity Factor of 1/sysindexes.nunique for an indexed_col = <literal> filter and a Selectivity Factor of 0.2 for a matches expression. Therefore a lower cost will be estimated by using the index on the = filter unless sysindexes.nunique < 5. (not very likely :-) ) >>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 Not the first one it comes across, but the one that it *expects* to be most selective. Even though your equality returns a large number of rows, I would wager that sysindexes.nunique > 5. The optimiser has no way of knowing that your MATCHES clause is highly selective. >>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. At the risk of fouling up other queries using this index, you could try: UPDATE sysindexes SET nunique = 4 WHERE ...; NB Note that you would have to redo this after UPDATE STATISTICS. graeme -- Disclaimer: The author's opinions are his own, and not necessarily those of Pyramid Technology Ltd. or Pyramid Technology Inc. --------------------------------------------------------------------------- -m------- Graeme Sargent Voice: +44 (0)252 373035 ---mmm----- Senior Database Consultant Fax : +44 (0)252 373135 -----mmmmm--- Pyramid Technology Ltd. Telex: Tell who??? -------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk --------------------------------------------------------------------------- We have the technology. The tricky bit is learning how to use it.