| no. of records in a table: count(*) or num_rows [message #494349] |
Sun, 13 February 2011 19:09  |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
Sirs/ Madame:
Please enlighten me with these 2 SQL statements in getting the
actual record count of a certain table.
1) select count(*) from MYTABLE1;
or
2) select num_rows from DBA_TABLES
where table_name = 'MYTABLE1';
Is it possible that this may produce discrepancies?
Thank you in advance.
|
|
|
|
|
|
| Re: no. of records in a table: count(*) or num_rows [message #494352 is a reply to message #494349] |
Sun, 13 February 2011 19:19  |
John Watson
Messages: 8964
Registered: January 2010
Location: Global Village
|
Senior Member |
|
|
try a test such as this:
create table t1 as select * from all_users;
select count(*) from t1;
select num_rows from user_tables where table_name='T1';
analyze table t1 compute statistics;
select count(*) from t1;
select num_rows from user_tables where table_name='T1';
delete from t1;
commit;
select count(*) from t1;
select num_rows from user_tables where table_name='T1';
analyze table t1 compute statistics;
select count(*) from t1;
select num_rows from user_tables where table_name='T1';
what do you think?
[update: typo]
[Updated on: Sun, 13 February 2011 19:20] Report message to a moderator |
|
|
|
|
|
|
|
| Re: no. of records in a table: count(*) or num_rows [message #494359 is a reply to message #494358] |
Sun, 13 February 2011 20:19 |
 |
BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal
|
Senior Member |
|
|
>Can you help me to know if tables are in their 'static' status?
a table is STATIC when no DELETE or INSERT against table occurs (the number of rows in the table does not change).
>I raised this topic because of the discrenpancies we've noted when our outsourcer executed their db reorganization activities.
I would expect discrepancies to exist PRIOR to the start of the "reorganization activities".
If so, the same discrepancies remain after the "reorganization activities".
[Updated on: Sun, 13 February 2011 20:25] Report message to a moderator |
|
|
|
|
|
| Re: no. of records in a table: count(*) or num_rows [message #494371 is a reply to message #494361] |
Mon, 14 February 2011 00:00 |
reym21
Messages: 241
Registered: February 2010
Location: Philippines
|
Senior Member |
|
|
Reorganization because of data purging they've made.
BTW, we've been stuck with our issue of using the 'num_rows' column in extracting the actual count of tables per schema...
This is our script:
REM GET ROWCOUNT OF ALL TABLES
select TO_CHAR(SysDate,'Mon/DD/YYYY HH:MI P.M.') todays_date from dual;
SPOOL /path/.../SITE1_tab_rowcnt.csv
prompt =============================================
prompt TABLES ROWCOUNT (ABC, CDE, STU) FOR SITE1
prompt =============================================
break on owner on report
compute sum of cnt on owner
compute sum of cnt on report
select owner, table_name, num_rows
from dba_tables
where owner in ('ABC', 'CDE', 'STU')
order by owner, table_name;
spool off
.. said script produce this output:
ABC ABC_ACTUAL_PARAMETERS 1477893
ABC_ADHOC_SQLS 1
ABC_BATCH_DEFINITIONS 34
CDE CDE_CLIENTS 2192402
CDE_SELECT_DISCRYS 1417
CDE_UPLOAD_TEMP 0
STU STU_DOCS_INVENTORY_DTLS 104
STU_DOCS_LOOSE_DTLS 149
STU_DOCS_LOOSE_DTLS_TRANS 181
How can we modify this script with the same output, but using the of 'count(*)' syntax? Everytime we include the 'count(*)' from our select command it gave us this error: "ORA-00937: not a single-group group function".
|
|
|
|
|
|
|
|
|
|
| Re: no. of records in a table: count(*) or num_rows [message #494395 is a reply to message #494384] |
Mon, 14 February 2011 03:47 |
cookiemonster
Messages: 13962
Registered: September 2008
Location: Rainy Manchester
|
Senior Member |
|
|
Considering your ancient version of the DB (you do know oracle desupported that a decade ago?) the following is probably your best bet:
set head off
set feed off
set echo off
set verify off
set lines 100
spool counts.sql
SELECT 'SELECT '''||table_name||''' table_name, count(*) FROM '||table_name||';'
FROM user_tables;
Run that in sqlplus, then run the resulting file in sqlplus.
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
