table scan

[MizzGail]

Probably a Stupid question. but.
What is a table scan?
When I look up stats on my informix tables using a canned query that informix gave me, it has a column listing the number of scans against a table. (the other columns are nrows, nread, nwrites..etc).
Are scans good or bad or is it all relative?

thanks

 

[olded]

MizzGail:

Nope, that's not a stupid question. I'm assuming you are referring to the seqscan column of the informix profile, onstat -p:

This is a full scan of the table meaning no indexes are used.

In my opinion, Yes, it is relative. A full scan of a table with 20 rows - no problem; A full scan of a table with 3,000,000 rows - a BIG problem.

A lot of seqscans may be a symptom of a missing index. I'd check to see if anybody is complaining about a slow query.

Regards,

Ed

 

[vpshriyan]

Hi,

Ed is right and ...

You can check the back-end for the tables having say 5000 or more rows and does not head an index on any columns; which may pose serious problem should there be a query running on such table(s) at any time.

select tabname, ti_nrows
from sysmaster:systabinfo,systables
where partnum=ti_partnum and ti_nrows > 5000 and
tabid not in(select tabid from sysindexes)

You may also evaluate the sequential scan statistics gathered at the SMI interface for the database server up time duration.

select dbsname, tabname, seqscans
from sysmaster:sysptprof
where seqscans > 0

Regards,
Shriyan

 

[MizzGail]

Thanks!