differences in num_rows
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: differences in num_rows

Hybrid View

  1. #1
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227

    differences in num_rows

    Hi all,

    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.


    Thanks,
    Leo
    leonard905
    leonard905@yahoo.com

  2. #2
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  3. #3
    Join Date
    Jun 2000
    Location
    dumfries,va,usa
    Posts
    227
    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.
    leonard905
    leonard905@yahoo.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width