Re: URGENT! How many rows will be returned?

Sue Han wrote: > > Hi, > > How can I know how many rows will be returned from the query > > SELECT * > FROM table1 t1, table2 t2 > where t1.column1 = t2.cloumn2;> > I know it can be done by > > SELECT count(*) > FROM table1 t1, table2 t2 > where t1.column1 = t2.cloumn2;> > But I don't want to touch the actual data to get the information. Is > there any statistics about that, and where is it stored? How can I get > it by program? > > I am wondering how the optimizer works it out. All of the databases listed in your crosspost use different statistics and data distributions or histograms to manage their optimizers and to ESTIMATE the number of rows returned from a query, indeed Informix reports this estimate in it's sqlca data structure when a cursor against the query is opened, however, these estimates are frequently way off. They depend on statistics gathered that may be out of date. If you updated the stats immediately prior to the query at a high enough level of detail (Informix for instance can use sampling from 1% of the data through 100% to determine the data distributions it uses) the estimates will likely be VERY close but I'd not rely on them to prevent me from overrunning a malloced array. No, the only way is to SELECT COUNT(*) ... as you suggest. Depending on the query itself, the level and quality of stats (affecting choices not returned values), the sophistication of the database's optimizer, and the existence of the appropriate indexes the engine may use the table's row counts (if there are no joins or filters), the indexes, or the data rows themselves to determine the count. Most servers provide a method for having the server relate its query plan so that you can improve the query and/or database design (ie add indexes) or provide optimizer hints or directives to produce a more efficient runtime. In Informix that is "SET EXPLAIN ON", in Oracle "EXPLAIN PLAN", etc. Informix's is the most sophisticated, powerful, and flexible of the optimizers on the market today (IMHO only DB2 comes close). Art S. Kagel

In article <380F5090.7BEDEB9@bloomberg.net>, Art S. Kagel <kagel@bloomberg.net> writes >Sue Han wrote: >> >> Hi, >> >> How can I know how many rows will be returned from the query >> >> SELECT * >> FROM table1 t1, table2 t2 >> where t1.column1 = t2.cloumn2;>> Will this return all the rows from the either table? If so update statistics on both tables and select nrows from systables where tabname "tablename" > >> I know it can be done by >> >> SELECT count(*) >> FROM table1 t1, table2 t2 >> where t1.column1 = t2.cloumn2;>> >> But I don't want to touch the actual data to get the information. Is If there are indexes on t1.column1 and t2.column Informix will do a Key-Only scan of the indexes (i.e. only read index keys and not actually the data rows) to get the query results. >> there any statistics about that, and where is it stored? How can I get >> it by program? >> >> I am wondering how the optimizer works it out. > >All of the databases listed in your crosspost use different statistics and >data distributions or histograms to manage their optimizers and to >ESTIMATE the number of rows returned from a query, indeed Informix reports >this estimate in it's sqlca data structure when a cursor against the query >is opened, however, these estimates are frequently way off. They depend >on statistics gathered that may be out of date. If you updated the stats >immediately prior to the query at a high enough level of detail (Informix >for instance can use sampling from 1% of the data through 100% to >determine the data distributions it uses) the estimates will likely be >VERY close but I'd not rely on them to prevent me from overrunning a >malloced array. > >No, the only way is to SELECT COUNT(*) ... as you suggest. Depending on >the query itself, the level and quality of stats (affecting choices not >returned values), the sophistication of the database's optimizer, and the >existence of the appropriate indexes the engine may use the table's row >counts (if there are no joins or filters), the indexes, or the data rows >themselves to determine the count. Most servers provide a method for >having the server relate its query plan so that you can improve the query >and/or database design (ie add indexes) or provide optimizer hints or >directives to produce a more efficient runtime. In Informix that is "SET >EXPLAIN ON", in Oracle "EXPLAIN PLAN", etc. > >Informix's is the most sophisticated, powerful, and flexible of the >optimizers on the market today (IMHO only DB2 comes close). > >Art S. Kagel -- David Williams