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

> 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 -