Re: Question on 7.12 Optimizer

V Crouch 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. Hi Did you run the same level of update statistics on both? I had a problem after using update statistics high on specific columns. The optimizer insisted on full table scans instead of using my indexes. Running update statistics medium drop distributions fixed this. (I think there is a bug logged for this but I'd have to check). At a guess, the optimizer will treat fragmented tables differently. It may sometimes take the view that a highly parallelized (sp?) full table scan beats using an index. William