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_at_fastmail.fm] 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_at_webloyalty.com> 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_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