count(*) diff from all_tables.num_rows in ?

[supermega]

When I do a count of all the rows in a table we have called production.dbug it tell me 1051
When I select all_tables.num_rows where table name = production.dbug it returns 937807
Why the huge difference?
It seems as if all_tables may be storing a high water mark?
Is this bad? The table is cleared every now and then( delete * ) should I do a truncate at that time?

Thanks,
SM

[nirasha]

Truncate is a good way to reset the HWM. This is only possible if you are deleting all rows. Remember truncate is a DDL and cannot be rolled back.

Nirasha

[supermega]

is that what num_rows in all_tables is, a high water mark?

Thanks again,
SM

[supermega]

*bump*

[leonard905]

Count * actually counts the indexes whereas select all will count each row on the table.

[marist89]

DBA_TABLES reflects the row counts as of the last time the table was analyzed. Issuing a select count(*) from tabxyz will give you the exact number of rows in the table. For example:
SQL> create table xyz (x char(10), y char(10), z char(10));

Table created.

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> analyze table xyz compute statistics;
Table analyzed.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME NUM_ROWS
------------------------------ ----------
XYZ 4

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> insert into xyz values ('x','y','z');

1 row created.

SQL> commit;

Commit complete.

SQL> select count(*) from xyz;

COUNT(*)
----------
8

SQL> select table_name, num_rows from user_tables;

TABLE_NAME NUM_ROWS
------------------------------ ----------
XYZ 4

SQL> analyze table xyz compute statistics;

Table analyzed.

SQL> select table_name, num_rows from user_tables;

TABLE_NAME NUM_ROWS
------------------------------ ----------
XYZ 8