Statistics on Partitioned Tables

Table NUM_ROWS vs Sum of Partition NUM_ROWS

WITH 
     -- List all user tables that have partitions
     TabList AS (
     SELECT DISTINCT TABLE_OWNER, TABLE_NAME 
       FROM dba_tab_partitions 
      WHERE TABLE_OWNER NOT IN ('SYSTEM','SYS') ORDER BY 1,2),
     -- Summarize partition details
     PartTotals AS (
     SELECT TABLE_OWNER, TABLE_NAME, SUM(NUM_ROWS) as PARTS_TOTAL_ROWS, MAX(LAST_ANALYZED) as MAX_PART_LAST_ANALYZED, MIN(LAST_ANALYZED) as MIN_PART_LAST_ANALYZED 
       FROM DBA_TAB_PARTITIONS 
      GROUP BY TABLE_OWNER, TABLE_NAME),
     -- Summarize Table vs Partition NUM_ROWS and ANALYZE_DATE info
     Summary AS (
     SELECT TABLIST.TABLE_OWNER, 
            TABLIST.TABLE_NAME, 
            DBA_TABLES.LAST_ANALYZED as TAB_LAST_ANALYZED,
            PartTotals.MIN_PART_LAST_ANALYZED,
            PartTotals.MAX_PART_LAST_ANALYZED,
            DBA_TABLES.NUM_ROWS as TABLE_ROWS,
            PartTotals.PARTS_TOTAL_ROWS,
            ABS(NVL(DBA_TABLES.NUM_ROWS,0)-NVL(PartTotals.PARTS_TOTAL_ROWS,0)) as RowDifference,
            GREATEST(DBA_TABLES.NUM_ROWS,PartTotals.PARTS_TOTAL_ROWS) as MaxNumRows
       FROM TabList, DBA_TABLES, PartTotals
      WHERE TabList.TABLE_OWNER=DBA_TABLES.OWNER and TabList.TABLE_NAME=DBA_TABLES.TABLE_NAME
        AND TabList.TABLE_OWNER=PartTotals.TABLE_OWNER AND TabList.TABLE_NAME=PartTotals.TABLE_NAME)
-- Calculate Percent Difference so we can see how much the Table vs Partition Num Rows differ
SELECT SUMMARY.*
       ,ROUND(Summary.RowDifference/NVL(MaxNumRows+1,1)*100,2) AS PctDiff 
  FROM SUMMARY 
 ORDER BY ROUND(Summary.RowDifference/NVL(MaxNumRows+1,1)*100,2);

Other Articles

http://blogs.oracle.com/optimizer/entry/maintaining_statistics_on_large_partitioned_tables