Re: DELETE does not use index

Bill Ennis wrote: > > Have you updated stats lately? > No you can't force a particular index. > } Hi, > } > } Any help with the following would be appreciated. > } > } I have a table (e.g. table_1) with two indexes, a unique index on the four > } primary key columns and a non-unique index on another column (e.g. > } column_x). When I execute the SQL: > } DELETE from table_1 where column_x = 'A' > } Informix does a full table scan instead of using the index. > } > } Is this a problem with the version of Informix - On-Line Dynamic Server > } 7.11.UC1 or is there some way of forcing the index to be used? > } > } Bill is right about the statistics. The optimizer will ignore the index if it determines, based on statistics, that a large enough percentage of the rows will be affected that it will most likely have to read most data pages anyway. In this way it saves the I/O's associated with reading the index pages. Out of date statistics can confuse the optimizer into making this choice erroneously. Art S. Kagel