0

I have Some Problem regarding getting row count .. If I do 

select NUM_ROWS,table_name from dba_tables;

I can get all tables and records count .. the problem is Suppose TABLE_NAMEE is my table name and I have 50 records and I have deleted 20 Records. When I do

select count(*) from TABLE_NAMEE;

I am getting 30 records but when I do 

select NUM_ROWS from dba_tables where table_name ='TABLE_NAMEE';

I am getting 50 records as my DB people update schema Once in every month . So how can I get all table names and their respective row count in ORACLE without using dba_tables , tab but I can use them for fetching table names not for row count ..

Joseph B
  • 5,519
  • 1
  • 15
  • 19
Chandrakanth
  • 39
  • 7
  • `num_rows` is only an *approximate* value (which is updated when the table is analyzed). It **never** contains the real number of rows. The only reliable way to get the row count is to use `select count(*)` –  May 01 '14 at 13:02

1 Answers1

1

Getting NUM_ROWS from the below query means that no.of rows value is updated by DBMS_STATS. So it does not contain the current number of rows in the table but an approximation calculated the last time DBMS_STATS was run.

SELECT table_name, 
       num_rows 
  FROM dba_tables 
 WHERE TABLE_NAME='NAME'

To update the latest num_rows value in the DBA_TABLES view execute 

exec dbms_stats.gather_schema_stats(ownname => 'NAME');`

NOTE: The above sentence "it does not contain the current number of rows in the table but an approximation calculated the last time DBMS_STATS was run." So even after update, it's still an approximation, not the exact row count. (But it could be a better approximation)

Using count(*) invokes a calculation of no. of rows from table. So this is slower but get's the correct results.

Timmetje
  • 7,641
  • 18
  • 36
  • 1
    On 11g this method would yield 100% accurate results. The 11g default statistics gathering method scans the entire table and counts each row. See [this post](https://blogs.oracle.com/optimizer/entry/how_does_auto_sample_size) explaining how the new AUTO_SAMPLE_SIZE works. – Jon Heller May 01 '14 at 17:35