count(*) diff from all_tables.num_rows in ?
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 6 of 6

Thread: count(*) diff from all_tables.num_rows in ?

Hybrid View

  1. #1
    Join Date
    Oct 2000
    Posts
    103

    Question

    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

  2. #2
    Join Date
    Nov 2000
    Posts
    205
    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
    Nirasha Jaganath

  3. #3
    Join Date
    Oct 2000
    Posts
    103

    Question

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

    Thanks again,
    SM

  4. #4
    Join Date
    Oct 2000
    Posts
    103
    *bump*

  5. #5
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    Count * actually counts the indexes whereas select all will count each row on the table.
    leonard905
    leonard905@yahoo.com

  6. #6
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092
    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

    Jeff Hunter
    marist89@yahoo.com
    http://marist89.blogspot.com/
    Get Firefox!
    "I pledge to stop eating sharks fin soup and will not do so under any circumstances."

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width