Re: Question on 7.12 Optimizer

William Hayman (williamh@sequent.com) wrote: : 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. I'd try checking the OPTCOMPIND config param (should be 0). Also, you could also try to turn off DSS (since your prod db is fragmented your prod Informix is trying to run this as a DSS query. Makes sure that your PDQPRIORITY is 0 and try running the query again. If that doesn't work. look at how you're doing the fragmentation. Joe -- --------------------------------------------------------------------------- Joe Lumbley(jlumbley@netcom.com) ---------------------------------------------------------------------------