Re: Analyze a table results in huge num_rows count

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_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.
> [neumann.webloyalty.com]
>

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