RE: Analyze a table results in huge num_rows count

For those who like to check if this is a problem in their environments
you can use this simple example

SQL> drop table test;

Table dropped.

SQL> create table test as select owner from dba_objects where rownum <
3000;

Table created.

SQL> execute dbms_fga.add_policy( object_schema => USER, object_name =>
'TEST', policy_name => '
AUDIT_TEST', audit_column => 'OWNER', enable =>TRUE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> EXEC
DBMS_STATS.GATHER_TABLE_STATS(USER,'TEST',NULL,.45554,FALSE,NULL,NULL,'D
EFAULT',TRUE,N
ULL,NULL,NULL,FALSE);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.00
SQL> SELECT num_rows from DBA_TABLES where table_name = 'TEST';

  NUM_ROWS
----------
    658340

Interestingly if the number of rows is 2500 or less the num_rows is
fine.


Yuval.


 

-----Original Message-----
From: GovindanK [mailto:gkatteri@xxxxxxxxxxx] 
Sent: Friday, September 30, 2005 10:35 AM
To: Arnon, Yuval; ORACLE-L
Subject: RE: Analyze a table results in huge num_rows count

Appreciated. 
On Fri, 30 Sep 2005 10:31:13 -0400, "Arnon, Yuval"
<Yuval.Arnon@xxxxxxxxxxxxxx> said:
> All,
> 
> Just to let you know I've figured what the problem is. This table is 
> being audited using FGA for one of the columns and that is what causes

> the huge discrepancy. Once I drop the policy (using execute 
> dbms_fga.drop_policy), the gather_table_stats works ok.
> I am going to open a TAR wih Oracle.
> 
> Thanks for the input.
> 
> 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.


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

Other related posts: