differences in num_rows
After updating statistics on a partitioned table using dbms_stats.gather_table_stats utility with percent = 10, the query select count(*) from table1 returned 7 million rows. I aslo got 7 million rows from select sum(num_rows) from dba_tab_partitions where table_name = 'TABLE1'.
However, num_rows in dba_tables for table1 returned only 4 million rows. Could you please explain why dba_tables is still showing 4 million. How can I get the statistics on dba_tables for table1 to be updated as the partitions.
btw.. last_analyzed in dba_tables is also different from the date I executed dbms_stats.gather_table_stats utility.
Analyze can be performed at the partition level, the table level, or both. This is controlled with the "granularity" option.
You might consider analyzing at the partition level, then updating the table level stats directly (using DBMS_STATS.SET_TABLE_STATS) based on the partition level stats.
The statement I used is below:
exec dbms_stats.gather_table_stats('user1',table1',null,10,false,'for all columns',null,'ALL',TRUE);
This should gather global and partition level stats. But is seemed to be only the latter is gathered.