Re: Search by index vs. sequential scan

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. Cheers, -- Mark. +----------------------------------------------------------+-----------+ | Mark D. Stock mailto:mdstock@MydasSolutions.com |//////// /| | Mydas Solutions Ltd http://MydasSolutions.com |///// / //| | +-----------------------------------+//// / ///| | |We value your comments, which have |/// / ////| | |been recorded and automatically |// / /////| | |emailed back to us for our records.|/ ////////| +----------------------+-----------------------------------+-----------+