Re: The dreaded sequential scan

Run your update statistics daily during off hours (3-6 am) after all programs that do mass/major updates/inserts/deletions have occurred. This is what we do. We have a 24X7 shop and a 10GB DB it takes almost 3 hours to run on Online Informix 5.02. FYI: When you talk number of rows this does not give an good idea of the DB size. You need to include the row length, plus the index size (length of row times number of rows). ------------------------------------------------------------------------- Cheryl Kendricks Internet: cherylk@spamis.jcdc.doleta.gov OR cherylk@prod1.jcdc.doleta.gov OR kendric@gwysmtp.jcdc.doleta.gov DTSI, Inc. Voice: 1-800-598-5008 Database Administrator - DOL Job Corps San Marcos, Texas ------------------------------------------------------------------------ On 28 Mar 1996, 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 >