Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Analyze a table results in huge num_rows count

Analyze a table results in huge num_rows count

From: Arnon, Yuval <Yuval.Arnon_at_webloyalty.com>
Date: Thu, 29 Sep 2005 17:15:03 -0400
Message-ID: <FE0ABB04D9872B4EA8B91DE36AAC449E027558D2@exchange04.webloyalty.com>


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_at_freelists.org, 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.

--
http://www.freelists.org/webpage/oracle-l
Received on Thu Sep 29 2005 - 16:18:11 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US