Re: Help with a SQL statement

Stuart,
No easy way to do this that I know of.

But, you can analyze the tables that you are interested in using ANALYZE TABLE <tablename> COMPUTE STATISTICS; The the NUM_ROWS column in DBA_TABLES would show the number of rows using: select OWNER,TABLE_NAME, NUM_ROWS
from DBA_TABLES;

To analyze a whole schema use:
execute DBMS_UTILITY.ANALYZE_SCHEMA('<schemaname>','COMPUTE');

To analyze a whole database use:
execute DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE');

You don't need to use COMPUTE but can use ESTIMATE to improve speed.

Remember the stats are only kept up to date by re-running the ANALYZE.

Remember the cost based optimizer uses these values when determining and execution path.

Remember to run the 'dbmsutil' script before using the DBMS_UTILITY package, this is in '${ORACLE_HOME}/rdbms/admin'.

--
Regards Pete

stuco_at_mailcity.com wrote in message <6tmeqi$l5k$1_at_nnrp1.dejanews.com>...


>I would like to issue one SQL or PL/SQL statement that would gather

>table_names and their respective row counts. How can this be done?? I know

>that I can write a SQL statement to create a bunch of SQL calls, but I

would


>rather only make one call to the database to save on overhead.

>

>thanks ever so much!

>

>Stuart Cowen

>Paladin Consulting - Dallas


>

>-----== Posted via Deja News, The Leader in Internet Discussion ==-----

>http://www.dejanews.com/rg_mkgrp.xp   Create Your Own Free Member Forum