Re: Question on 7.12 Optimizer

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. > > 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 YMMV, but it is an easy thing to try... Paul. <plkapp@erols.com>