Re: Sequential scans on a one row table ?

"Reardon, Andrew J" wrote: > > Hi all > > Just had a look at my "onstat -p" and it was showing a terribly large number > of seqscans (~100K), so I went in and had a look at the offending table. > Turns out the table with the most seqscans against it has only got *one row* > in it. There were some other tables having seqscans done on them, ranging in > size from about 10 to 10K rows. > > My setup is: IDS 7.24.UC6 on Solaris 2.6 on a Sun E450 (3 CPU) with 1GB RAM. > The database is on cooked UFS disks. I run update stats high nightly > (probably overkill but hey I've got some spare CPU cycles so why not:) > > My questions are: > > 1. Is it just a recording error that a one row table can contribute to the > seqscans count ? Would I be correct in discounting any seqscans on a one row > table when I'm looking at things like sysptprof in SMI ? WHAT do people have against sequential scans? They are not automatically bad for performance, as illustrated by a one row table. Assuming your one row fits into one page, you would DOUBLE IO if you performed an index read. Think about it. > 2. How many rows should a table be before sequential scans on that table > hurt performance ? eg if a 100 row table was being seqscanned, would you be > inclined to put an index on that table as a matter of course ? It depends on the available indexes, the cost of reading those indexes, the number of rows you are reading, etc. If you read very large tables for example, the optimiser might well use a hash join, which would ALSO use sequential scans. Rule number 987: If it ain't broke, don't fix it! :-) Cheers, -- Mark. +----------------------------------------------------------+-----------+ |Mark D. Stock - Informix SA http://www.informix.com |//////// /| |mailto:mdstock@informix.com http://www.informix.com/idn |///// / //| |http://www.iiug.org +-----------------------------------+//// / ///| | Tel: +27 11 807 0313 |What year 2000 bug? year 2000 bug? |/// / ////| | Fax: +27 838250 2325 |year 2000 bug? year 2000 bug? year |// / /////| |Cell: +27 83 250 2325 |2000 bug? year 2000 bug? year 1900 |/ ////////| +----------------------+-----------------------------------+-----------+