RE: DBA_TABLES.NUMROWS not equal Count(*) ... Why?

It appears you are getting the defaults. The first line is setting the stats:  

SQL> exec dbms_stats.set_table_stats('GENERAL','GUROUTP');  

Then you look then up:  

dbms_stats.get_table_stats('GENERAL','GUROUTP',numrows=>lNumRows, numblks=>lNumBlks, avgrlen=>lAvgRLen);  

I don't recall what the defaults of set_table_stats are right now, but I'd bet your paycheck that is what is going on. Perhaps you meant to do this first:  

SQL> DBMS_STATS.GATHER_TABLE_STATS('GENERAL','GUROUTP');       Ric Van Dyke

Hotsos Enterprises

Cell 248-705-0624

Hotsos Symposium, be there:

http://www.hotsos.com/portal/events/SYM06

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Sam Bootsma Sent: Thursday, February 16, 2006 4:12 PM To: oracle-l_at_freelists.org
Subject: DBA_TABLES.NUMROWS not equal Count(*) ... Why?  

Dbms_stats tells me there are 2000 rows, but count(*) says there are about 3.2 million. Can anybody explain to me what is going on? Please see below. Thanks.    

SQL> exec dbms_stats.set_table_stats('GENERAL','GUROUTP');  

PL/SQL procedure successfully completed.  

SQL> SQL> declare

  2 lNumRows number;

  3 lNumBlks number;

  4 lAvgRLen number;

  5 begin

  6 dbms_stats.get_table_stats('GENERAL','GUROUTP',numrows=>lNumRows, numblks=>lNumBlks, avgrlen=>lAvgRLen);

  7 dbms_output.put_line('No. of rows: ' || lnumrows);

  8 dbms_output.put_line('No. of blks: ' || lnumblks);

  9 dbms_output.put_line('Avg row length: ' || lavgrlen);

 10 end;

 11 /

No. of rows: 2000

No. of blks: 100

Avg row length: 100  

PL/SQL procedure successfully completed.  

SQL> SQL> select count(*) from general.guroutp;  

  COUNT(*)

   3219298  

1 row selected.    

Sam Bootsma

Oracle DBA

George Brown College

sbootsma_at_gbrownc.on.ca <mailto:sbootsma_at_gbrownc.on.ca>

416-415-5000 x4933  

--
http://www.freelists.org/webpage/oracle-l