Analyze a table results in huge num_rows count

  • From: "Arnon, Yuval" <Yuval.Arnon@xxxxxxxxxxxxxx>
  • To: "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
  • Date: Thu, 29 Sep 2005 17:15:03 -0400

Hi.
I have a heap table with 3.3 million rows. When this table is analyzed
with the old "analyze table .... estimate statistics." the num_rows is
pretty accurate. 
But when I run the following command the num_rows shows as 1,008,678,067
!!!!!. 
 
EXEC
DBMS_STATS.GATHER_TABLE_STATS(USER,TABLE_NAME,NULL,.35554,FALSE,NULL,NUL
L,'DEFAULT',TRUE,NULL,NULL,NULL,FALSE);
 
I've also noticed that if I change the ESTIMATE_PERCENT to a lower
number, the num_rows increases to even higher numbers (more than 2
billion !!!).
 
This of course impacts the execution plan.
 
Table has only number. date and varchar2 columns . Number of column is
12.
 
We are on Oracle 9.2.0.6. 
 
Any idea what's going on??
 
TIA
 
Yuval.
 

This transmission may contain information that is privileged, confidential and 
exempt from disclosure under applicable law.  If you, oracle-l@xxxxxxxxxxxxx, 
are not the intended recipient, you are hereby notified that any disclosure, 
copying, distribution, or use of the information contained herein (including 
any reliance thereon) is STRICTLY PROHIBITED.  If you received this 
transmission in error, please immediately contact the sender and destroy the 
material in its entirety, whether in electronic or hard copy format.


Other related posts: