Re: Analyze a table results in huge num_rows count
- From: Yechiel Adar <adar76@xxxxxxxxxxxx>
- Date: Fri, 30 Sep 2005 10:03:48 +0200
Hello Yuval
I hope you are aware that your sample is 1/3 of one percent only.
My GUESS is that you have very different distribution of data in the
blocks.
If you have heavy insert/delete activity and some of the blocks are full
and some are partially empty, if Oracle visits a few blocks that are
full, it will think that all blocks are full and that will increase the
num_rows.
Adar Yechiel
Rechovot, Israel
Arnon, Yuval wrote:
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,NULL,'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.
[neumann.webloyalty.com]
--
//www.freelists.org/webpage/oracle-l
Other related posts:
