Object Statistics - Troubleshooting Oracle Performance - page 248

NUM_DISTINCT GLOBAL_STATS
------------ ------------
28 NO

• The number of distinct values at the partition level (here for a single partition), because they

were gathered as derived statistics, is also wrong:

SQL> SELECT count(DISTINCT sp)
2 FROM t PARTITION (q1);

COUNT(DISTINCTSP)
--------------------
100
SQL>
SQL> SELECT num_distinct, global_stats
2 FROM user_part_col_statistics
3 WHERE table_name = 'T'
4 AND partition_name = 'Q1'
5 AND column_name = 'SP';

NUM_DISTINCT GLOBAL_STATS
------------ ------------
28 NO

• The statistics about the number of distinct values at the subpartition level (here for a single

partition) are right:

SQL> SELECT 'Q1_SP1' AS part_name, count(DISTINCT sp) FROM t SUBPARTITION (q1_sp1)
2 UNION ALL
3 SELECT 'Q1_SP2', count(DISTINCT sp) FROM t SUBPARTITION (q1_sp2)
4 UNION ALL
5 SELECT 'Q1_SP3', count(DISTINCT sp) FROM t SUBPARTITION (q1_sp3)
6 UNION ALL
7 SELECT 'Q1_SP4', count(DISTINCT sp) FROM t SUBPARTITION (q1_sp4);

PART_NAME COUNT(DISTINCTSP)
--------- -----------------
Q1_SP1 20
Q1_SP2 28
Q1_SP3 25
Q1_SP4 27

SQL> SELECT subpartition_name, num_distinct, global_stats
2 FROM user_subpart_col_statistics
3 WHERE table_name = 'T'
4 AND column_name = 'SP'
5 AND subpartition_name LIKE 'Q1%';