Re: Search by index vs. sequential scan

Mark D. Stock <mdstock@mydassolutions.com> wrote: > > Robin Munn wrote: >> Ladies and gentlemen, >> >> I've been running into a very interesting problem, and I'd appreciate >> your advice. When is it better to have a sequential scan vs. using an >> index in a query? To be specific, if we change the OPTCOMPIND parameter >> in /usr/informix/etc/onconfig from 2 to 0 (thereby telling the optimizer >> to prefer indexes over sequential scans), what kinds of queries are >> likely to suffer performance penalties? > > First, the optimiser will rely on you supplying up to date statistics by > running UPDATE STATISTICS regularly. > > Second, when the optimiser believes you are going to read the majority > of the rows in a table, then a sequential scan is going to be more > efficient that reading indexes first. If you are going to read a small > percentage of rows in the table, then an index read is going to be more > efficient. > > The recommendation you saw for setting OPTCOMPIND to 0 is for OLTP type > systems that generally read small percentages of tables. Setting > OPTCOMPIND to 2 would be beneficial to batch type processing and DSS > queries. > > If you find a particular query suffering from your global setting, then > you can always override that value by setting the environment variable. Thanks; that's very helpful. Most of our queries tend to pick out a single row (or a few rows) from a table, so OPTCOMPIND of 0 does make sense for us. And given that it took care of the locking problem I described in my earlier post, I think we'll keep it. Thanks also for the environment variables suggestion. Do you mean something like this? bash$ OPTCOMPIND=2 cat batch_query.sql | dbaccess -- Robin Munn <rmunn@pobox.com> http://www.rmunn.com/ PGP key ID: 0x6AFB6838 50FF 2478 CFFB 081A 8338 54F7 845D ACFD 6AFB 6838