Re: Select Select

mcglew_at_jolt.att.com wrote:
>
> In response to
> "I want to get a list of all tables and the number of records in those
> tables for just the tables that have records in them."
>
> drop table this_temp_table;
> create table this_temp_table (
> num integer,
> table_name varchar2(30));
>
> set heading off
> set pagesize 0
> set linesize 150
> set feedback off
> set termout off
> spool temp_table_1.sql
> select 'select ''insert into this_temp_table (num,table_name) values ('','
> x,
> ' count(*),'','''''||table_name||''''');'' from '||table_name||';'
> from user_tables;
> spool off
> spool temp_table_2.sql
> start temp_table_1.sql
> spool off
> spool insert_them_temp.lst
> start temp_table_2.sql
> spool off
> start insert_them_temp.lst
> set termout on
> spool count_them.lst
> select * from this_temp_table where num >0
> order by num desc
> /
> drop table this_temp_table;
> spool off
>
> -------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet

If you're a DBA you can als run a analyze compute statistics on all the tables of the database and after that you can query ALL_TABLES, DBA_TABLES or USER_TABLES for the numbers. If you're not a DBA then ask youre DBA if he has done it already or if he is willing to.

Some arguments pro & contra:

PRO: Cost based optimizer works much better :-) it boosts performance :-))
CONTRA: If you have a BIG databse with a LOT of tables then it will take a while. The last time i used it on a database (284 tables about 15 million records) it took about 2 hours on a ALPHA workstation

Hera are two scripts which will do the working for you:

FIRST: generate a analyze compute statistics script

SET HEADING OFF;
SET PAGESIZE 0
SET ECHO OFF;
SET FEEDBACK OFF;
SPOOL ANALYZE_TABLES.SQL
SELECT 'ANALYZE TABLE '||TABLE_NAME||' COMPUTE STATISTICS;' FROM ALL_TABLES;
SPOOL OFF; SECOND: RUN THE GENERATED SCRIPT ANALYZE_TABLES.SQL THIRD: USE THIS SCRIPT TO GET ALL TABLES WITH A NUMBER OF RECORDS GREATE THEN ZERO. SET HEADING OFF;
SET PAGESIZE 0
SET ECHO OFF;
SET FEEDBACK OFF;
SPOOL NUMBER_OF_ROWS.LST
SELECT TABLE_NAME, NUM_ROWS
FROM ALL_TABLES
WHERE NUM_ROWS>0
ORDER BY NUM_ROWS DESC;
SPOOL OFF; Personally i find this method easier because most DBA's use the cost-based optimizer and will update the statistics periodically. I myself update them every weekend. So the tables ALL_TABLES, USER_TABLES & DBA_TABLES are allways up to date.

				Hope to have given some helpfull info,
				With friendly regards, 

-- 
                                               G.R. van der Ploeg
                                              (G.R.v.d.Ploeg_at_imn.nl)
                                               IMN NV Zeist, Holland
                                               ORACLE DBA & Developer