Skip to Main Content

Breadcrumb

Dev Live Dev Intro

We are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August and September. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Tom Kyte

Thanks for the question, Marcio.

Asked: September 02, 2003 - 2:36 pm UTC

Answered by: Tom Kyte - Last updated: September 03, 2003 - 9:58 am UTC

Category: SQL*Plus - Version: 9.2.0

Viewed 1000+ times

You Asked

Tom,

1) Is this best way (performance) to get count(*) from all tables?

scott@MRP9I1> create or replace function get_rows( p_tname in varchar2 ) return number
2 as
3 l_columnValue number default NULL;
4 begin
5 execute immediate
6 'select count(*)
7 from ' || p_tname INTO l_columnValue;
8 return l_columnValue;
9 end;
10 /

Function created.

scott@MRP9I1>
scott@MRP9I1> select table_name, get_rows(table_name) nrows
2 from user_tables
3 /

TABLE_NAME NROWS
------------------------------ ----------
BONUS 0
DEPT 4
EMP 15
SALGRADE 5

4 rows selected.

2) does @showsql show imp/exp activity? Guess not, how can I see this activity?

Best regards,
Marcio

and we said...

1) no, static sql just:

select count(*) from bonus

is going to be "faster" then


select table_name, get_rows(table_name) nrows
from user_tables where table_name = 'BONUS'


but, if you want to get the count of all tables right now -- that would be the easiest way...


2) yes it does -- imp/emp just does sql -- imp spends lots of time issuing "create index" and "constraints" -- which means it'll show active in showsql but not have any sql to show for it (a peek at v$session_longops can be helpful in figuring out what it is doing as well)

and you rated our response

  (2 ratings)

Is this answer out of date? If it is, please let us know via a Review

Reviews

Count(*) from all tables

September 03, 2003 - 7:03 am UTC

Reviewer: Angus McTavish aged 75 from Cumbria, England

Run this in sqlplus to get a count of all tables:

store set store replace
set lines 100
set head off
set feed off
set term off
set wrap off
COLUMN DUMCOL FORMAT A30 TRUNC

spool cnt.sql
select 'select '''||table_name||''' DUMCOL, count(*) from '||table_name||';' from user_tables
/
spool off
spool counts.lst
@cnt
spool off
CLEAR COLUMNS
@store
ed counts.lst

IMHO -- Function is better

September 03, 2003 - 9:58 am UTC

Reviewer: Marcio from Br

I think more easer function -- to get all of count(*)
Consider:
Tables rows
-----------------------------------------------------
LOCALIDADE 24275
LOCAL_ANATEL 37000
LOCAL_DETRAF 24275


*-------------------
* script method
*-------------------

store set store replace
set lines 100
set head off
set feed off
set term off
set wrap off
COLUMN DUMCOL FORMAT A30 TRUNC

spool cnt.sql
select 'select '''||table_name||''' DUMCOL, count(*) from '||table_name||';'
from user_tables
where table_name like 'LOCA%'
/
spool off
spool counts.lst
@cnt
spool off
CLEAR COLUMNS
@store

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 10 0.00 0.00 0 0 0 0
Execute 11 0.00 0.00 0 0 0 3
Fetch 9 0.62 0.61 0 821 0 7
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 30 0.62 0.62 0 821 0 10


*----------------------
* Function Method:
*----------------------
select table_name, get_rows(table_name)
from user_tables
where table_name like 'LOCA%'
/


OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 4 0.01 0.00 0 0 0 0
Execute 4 0.00 0.00 0 0 0 0
Fetch 4 0.65 0.63 0 771 0 4
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 12 0.66 0.64 0 771 0 4

--
Anyway thanks for suggestion
Marcio