Re: SQL Count

In article <8778@emory.mathcs.emory.edu> foxj@moscow.cc.bellcore.com (fox,james p) writes: > > I have come across what seems like a simple problem but I can't > think of an easy solution to it. > > I have a table with the following columns: > FIELD1 > FIELD2 > FIELD3 > > and an Ace report select of: > SELECT UNIQUE FIELD1, FIELD2 from table;> > Before I execute this report I would like to know how many rows > will be selected but it seems impossible to use count as I > cannot specify two fields on the UNIQUE. By the way I am using > ESQL/C. You can learn quite a bit by selecting from the system catalog. The basic info is in systables.nrows. If you select that: SELECT nrows FROM informix.systables WHERE tabname = "name_of_table" -- AND owner = "table_owner" in an ansi-compliant dbthis at least gives you an upper bound! In fact the fraction of unique rows in a large table should be a stable statistic over time. Suppose it turns out to be 0.72. You get a good approximation by SELECT nrows * 0.72 FROM informix.systables, etc. etc. But if you are using OnLine, and you put an index on the columns of interest, you can get the number directly! The nunique column of sysindexes has the count of unique keys in an index. (This column is not supported by the SE engine.) SELECT nunique FROM informix.sysindexes WHERE idxname = "name_of_index" AND tabid = ( SELECT tabid FROM informix.systables WHERE tabname = "name_of_table" -- AND owner = "table_owner" for ansi db ) So with OnLine, and if you UPDATE STATISTICS regularly of course,you can have the number for the cost of an index on those columns.