Re: [Q] How do I force usage of index

Daniel Williams wrote: > > > I have an OWS 7.12 application comprising 60,000 records (on a Sun > > Enterprise) on which even simple indexed join queries run very slowly > > (i.e., 'where tablea.id = tableb.id' will take 30 seconds). > > > > Using SET EXPLAIN ON it seems that none of the queries are using the > > indexes (all sequential scans). I have data files, indexes and blobs > > separated into different dbspaces and update statistics is run ^^^^^^^^^^^^^^^^^^^^^^^^ > > regularly. ^^^^^^^^^^ > > > > Is there any way I can force usage of the indexes to see if it makes any > > difference (presuming that the the query optimizer has decided not to > > use them for some reason) ? > > Have you run UPDATE STATISTICS (see SQL manuals for more details). If you don't do this, the > query optimiser has no idea how to really access the data. You can also issue the statement > ALTER INDEX ixname TO CLUSER; to place your data in accordance with the index.> > Also, look at the parameter OPTCOMPIND (see admin guide for more details). This influences the > access path chosen by the query optimiser. This includes whether each path is evaluated or only > the step that appears to be the best at each level. > > Sorry for not providing more information, but the manuals contain everything you'll need. > > -- > Danny > > - remove the # from my e-mail address if you wish to use it - The Question was: how can I force the optimizer to use certain query paths !! This is not a "do UPDATE STATISTICS" issue and the answers are certainly not found in any manual. I found some answers to this question at http://www.weideneder.de/informix/faq/ftos.html hope this helps !!