Re: The dreaded sequential scan

ka@socrates.hr.att.com (Kenneth Almquist) wrote: >Informix 7.10.UC1 has a tendency to perform sequential scans (which >are *SLOW*) even when an index is available. According to Informix, >the way to avoid this problem is to run UPDATE STATISTICS after major >changes are made to a table. My problem is that I'm working on a >project which is supposed to store 20 million records using Informix. >First, what happens when you run UPDATE STATISTICS on a live data base >this size? Our system doesn't have any scheduled down time, so that >we need to support queries (and probably updates) of the data base >while UPDATE STATISTICS is running. >Second, how do we know when to run UPDATE STATISTICS? Running UPDATE >STATISTICS after the system starts performing sequential scans is not >an acceptable long term solution; we have to avoid sequential scans in >the first place. > Kenneth Almquist You can try updating statistics for the column search is done on. The other possibility is with 20 million rows you might look at fragmenting the table by expression based on your most used where clause. Then informix could eliminate any fragments that did not match the search. Also if these fragments are on different disk even if it had to do a scan it could take advantage of the 7.1 PDQ features. -- Tolly Brumbelow, Informix DBA email:tolly.brumbelow@bridge.bellsouth.com