RE: DBA_TABLES.NUMROWS not equal Count(*) ... Why?
- From: "Ric Van Dyke" <ric.van.dyke@xxxxxxxxxx>
- To: <sbootsma@xxxxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Thu, 16 Feb 2006 15:42:55 -0600
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@xxxxxxxxxxxxx
[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Sam Bootsma
Sent: Thursday, February 16, 2006 4:12 PM
To: oracle-l@xxxxxxxxxxxxx
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@xxxxxxxxxxxxx <mailto:sbootsma@xxxxxxxxxxxxx>
416-415-5000 x4933
Other related posts:
