Re: Question on 7.12 Optimizer

Paul Kapp wrote: > > Lam Yin Yui wrote: > > > > V Crouch (tiger@sky.net) wrote: > > : Could you please help me solve a puzzle? I have a "test" database and a > > : "production" database, both which are configured the same. I have a table > > : with about 1.3 million rows in it, in both test and prod. The table > > : contains names and addresses. I have an application that does a lookup > > : based on the first part of the name (i.e. SELECT ...WHERE name is LIKE > > : ...). For both test and prod, I've recently built an index on the lookup > > : column, and I've updated statistics at the database, table, and column > > : level. The only difference between test and prod is that the test table is > > : fragmented across 3 dbspaces, while the prod one is not. > > > > : The mystery is this: On test, my query uses the index, while the prod > > : query does a sequential scan. Any thoughts on why this might occur, or > > : what I can do to force the optimizer to use the index? I would really > > : appreciate some assistance. Fragmentation may cause a different optimisation strategy. Does it perform slower? If not, it got it right. > > I also face this problem on update statistics medium or high. In my opinion, if you would like to use index searching, you better update statistics medium; though the performance may not be optimize. > > We encountered a pretty mean bug in 7.11 & 7.12 that gave bizzarre > optimization, and produced incorrect query results when using some OUTER > joins. The workaround for us was to limit OPTCOMPIND to zero for all of > our queries. There are a number of similar problems related to > OPTCOMPIND values greater than zero in 7.1X version until 7.13/7.14 This is the whole point isn't it? I've seen a few posts where people created indexes then got upset when the optimiser didn't use it. I've also seen a few people insisting OPTCOMPIND be set to 0. If there IS a bug or the performance IS worse, then yes. But I would stick to the old rule: If it isn't broken, don't fix it. IMO the optimiser works very well, as long as the statistics are up to date. Cheers, -- Mark. +------------------------------------------------------------------+ |Mark D. Stock - The West Solutions Group http://www.west.co.za | | The Informix FAQ is at http://www.iiug.org | |mailto:marks@west.co.za +-----------------------------------------+ |Tel: +27 11 803 2151 |If it doesn't work, force it! | |Fax: +27 11 803 2189 |If it breaks, it needed replacing anyway!| |Cell: +27 83 250 2325 |Well, that's how I code anyway! | +------------------------+-----------------------------------------+