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 If you perform "UPDATE STATISTICS FOR TABLE tabname" it shouldn't take too long. A good time to do it is after a considerable change in the table size. Another reason for the Seq. scan can be hush joins. See my answer to another article. I hope this helps, Roy G.