Re: Cost Based Optimizer

Harry Bochner laments: > > 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 consistent with what I've seen. The cost-based optimizer, at least in SE, > seems to consider "matches" a very poor choice even when there is an index on > that field, and it will prefer almost any other criterion, even when the > "matches" criterion is by far the best choice. > In some of the cases I've run into, the "primitive" optimizer in 2.10.x made > better choices than the new cost-based optimizer :-( > > Looking through the old articles I've got saved, I don't see any hints for > dealing with this. If anyone has some good techniques for coercing the optimizer > into doing something more sensible, I'd like to hear about it. > I liked Jonathan's recent suggestion about doing: field[1,3]="abc" instead of field matches "abc*" Of course this implies that you have to parse your where clause and determine what subscript to use and when to use it - which is a pain. Perhaps the best strategy is to design the database schema in such a way that you are always reading a dual columned short table somewhere to figure out what integer key to use in the second/third/etc. table and therefore rely on design instead of indices to speed things up. One of our major keys here is a char(18) and a char(8) combination. In one application (fairly small) we use these as keys - the rewards in terms of programming ease are high. In a sister application (very large) they convert these two keys immediately through an index table and then use a resulting integer key against all other tables - the rewards in access speed there are pretty astounding. cheers j. _____________________________________________________________________________ Jack Parker - Contractor |"Is it weakness of intellect birdie" Hewlett Packard, BSMC Boise, Idaho, USA|I cried, "or a tough little worm in jparker@hpbs2561.boi.hp.com |your little inside", with a shake of (208) 396-5388 (W) (208) 384-1623 (H) |his head he sadly replied: | "Oh willow, Oh willow, tit willow". _____________________________________________________________________________ Any opinions expressed herein are my own and not those of my employers. _____________________________________________________________________________