Re: Select count(*)

clay@panix.com (Clay Irving) responds to naomi@qip.anasazi.com (Naomi Walker): -> ->In <21fkdvINNi2i@emory.mathcs.emory.edu> naomi@qip.anasazi.com writes: -> ->>The problem I have with setting ISOLATION is that it affects the entire ->>database, and sometimes I only want a particular table. A silly thought ->>though. To get the number of rows in a table, sometimes I : -> ->> SELECT nrows ->> FROM systables ->> WHERE tabname = "table_name"; -> ->>Its much faster than count(*). -> ->I assume that update statistics has been run recently also? Or does update ->statistics have no effect on nrows?... -> ->-- ->Clay Irving As I understand it, the purpose of UPDATE STATISTICS is to update the value in systables.nrows. This value is in turn used by the cost based optimizer. Thus, UPDATE STATISTICS [FOR TABLE table_name] should be run periodically (at least for tables that change) to keep your database optimizer "tuned". UPDATE STATISTICS can take a long time because it does a COUNT(*) on everytable in the database. Using the FOR TABLE clause speeds things up by only doing the indicated table, but is still as long as a COUNT(*) on that table. Regards, Alan +---------------------------+--------------------------------------------+ | R. Alan Popiel | Internet: alan@den.mmc.com | | Martin Marietta, Tech Ops | Voice: 303-977-9998 | | P.O. Box 179, M/S 5422 | My opinions may not reflect Martin policy. | | Denver, CO 80201-0179 USA | In fact, we often disagree. | +---------------------------+--------------------------------------------+