大家都知道，子分区有两种，一种是分区为RANGE，子分区为HASH，另一种是分区为RANGE，子分区为LIST。在10g数据库中，如果在使用dbms_stats收集统计信息时，如果没有显式指定granularity(粒度)，那么granularity就会取自dbms_stats配置：
而其缺省值是“AUTO”，而不再是9i下的”DEFAULT”：
SQL> select dbms_stats.get_param('granularity') param from dual;
PARAM
------------------------------
AUTO
SQL> select dbms_stats.get_param('granularity') param from dual;
PARAM
------------------------------
AUTO
而10g自带的自动收集统计信息的任务“GATHER_STATS_JOB”，其granularity同样是取自granularity param。当然可以通过下面的SQL来更改其值：
SQL> exec dbms_stats.set_param('granularity','global and partition');
SQL> exec dbms_stats.set_param('granularity','global and partition');
这样更改后，dbms_stats默认就会收集表以及分区级统计信息，不收集子分区级统计信息。
那么，granularity=auto时，到底是怎么样的呢?前面说到了子分区是以list方式分区时，那么就会收集子分区级统计信息，其言外之意就是如果子分区是以hash方式分区时就不会收集子分区统计信息了。到底是不是这样呢?下面做个测试，测试环境是Oracle 10.2.0.4 for Linux AS4：
SQL> create table t1
2 partition by range(object_id)
3 subpartition by hash(data_object_id)
4 subpartitions 4
5 ( partition p1 values less than(10000),
6 partition p2 values less than(20000),
7 partition p3 values less than (maxvalue)
8 )
9 as select * from dba_objects;
Table created.
SQL> create table t2
2 partition by range(object_id)
3 subpartition by list(object_type)
4 subpartition template(
5 subpartition sp1 values ('TABLE'),
6 subpartition sp2 values ('INDEX'),
7 subpartition sp3 values ('VIEW'),
8 subpartition sp4 values (DEFAULT)
9 )
10 ( partition p1 values less than(10000),
11 partition p2 values less than(20000),
12 partition p3 values less than (maxvalue)
13 )
14 as select * from dba_objects;
Table created.
SQL> create table t1
2 partition by range(object_id)
3 subpartition by hash(data_object_id)
4 subpartitions 4
5 ( partition p1 values less than(10000),
6 partition p2 values less than(20000),
7 partition p3 values less than (maxvalue)
8 )
9 as select * from dba_objects;
Table created.
SQL> create table t2
2 partition by range(object_id)
3 subpartition by list(object_type)
4 subpartition template(
5 subpartition sp1 values ('TABLE'),
6 subpartition sp2 values ('INDEX'),
7 subpartition sp3 values ('VIEW'),
8 subpartition sp4 values (DEFAULT)
9 )
10 ( partition p1 values less than(10000),
11 partition p2 values less than(20000),
12 partition p3 values less than (maxvalue)
13 )
14 as select * from dba_objects;
Table created.
我们先建再从个测试表，表T1是RANGE+HASH方式的复合(组合)分区表，表T2是RANGE+LIST方式的复合分区表。
下面将”granularity” param重新设回为”auto“，然后收集T1和T2的统计信息：
SQL> exec dbms_stats.set_param('granularity','auto');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t2');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.set_param('granularity','auto');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t1');
PL/SQL procedure successfully completed.
SQL> exec dbms_stats.gather_table_stats(user,'t2');
PL/SQL procedure successfully completed.
接下来我们看看T1和T2子分区的统计信息，首先是T1表的：
***************
SubPartition Level
***************
Partition SubPartition Number Chain Average
Name Name of Rows Blocks Count Row Len
--------------- --------------- -------------- -------- -------- -------
P1 SYS_SUBP57
P2 SYS_SUBP61
P3 SYS_SUBP65
P2 SYS_SUBP62
P3 SYS_SUBP66
P1 SYS_SUBP58
SYS_SUBP59
P2 SYS_SUBP63
P3 SYS_SUBP67
P1 SYS_SUBP60
P3 SYS_SUBP68
P2 SYS_SUBP64
***************
SubPartition Level
***************
Partition SubPartition Number Chain Average
Name Name of Rows Blocks Count Row Len
--------------- --------------- -------------- -------- -------- -------
P1 SYS_SUBP57
P2 SYS_SUBP61
P3 SYS_SUBP65
P2 SYS_SUBP62
P3 SYS_SUBP66
P1 SYS_SUBP58
SYS_SUBP59
P2 SYS_SUBP63
P3 SYS_SUBP67
P1 SYS_SUBP60
P3 SYS_SUBP68
P2 SYS_SUBP64
可以看到子分区没有任何统计信息，再看看T2表的子分区：
***************
SubPartition Level
***************
Partition SubPartition Number Chain Average
Name Name of Rows Blocks Count Row Len
--------------- --------------- -------------- -------- -------- -------
P2 P2_SP1 370 6 0 92
P3 P3_SP1 346 5 0 86
P1 P1_SP1 818 11 0 82
P3 P3_SP2 303 4 0 88
P1 P1_SP2 916 12 0 85
P2 P2_SP2 430 6 0 91
P2_SP3 252 4 0 86
P3 P3_SP3 517 7 0 87
P1 P1_SP3 2,839 34 0 78
P2 P2_SP4 8,722 124 0 95
P1 P1_SP4 4,973 64 0 86
P3 P3_SP4 24,900 352 0 96
***************
SubPartition Level
***************
Partition SubPartition Number Chain Average
Name Name of Rows Blocks Count Row Len
--------------- --------------- -------------- -------- -------- -------
P2 P2_SP1 370 6 0 92
P3 P3_SP1 346 5 0 86
P1 P1_SP1 818 11 0 82
P3 P3_SP2 303 4 0 88
P1 P1_SP2 916 12 0 85
P2 P2_SP2 430 6 0 91
P2_SP3 252 4 0 86
P3 P3_SP3 517 7 0 87
P1 P1_SP3 2,839 34 0 78
P2 P2_SP4 8,722 124 0 95
P1 P1_SP4 4,973 64 0 86
P3 P3_SP4 24,900 352 0 96
可以看到T2表的子分区是有统计信息的。 这也证明了前面说到的，如果granularity为auto，list类型的子分区会收集统计信息，而hash类型的子分区不收集统计信息。
大多数Oracle数据库里面没有使用子分区，就算有，也比较少量。不过下面一个库的一些统计数据，是我写这篇文章的初衷：
SQL> select sum(bytes)/1024/1024 size_mb from dba_data_files where tablespace_name='SYSTEM';
SIZE_MB
----------
10233.9922
SQL> select /*+ parallel(a,8) */ count(*) from hist_head$ a;
COUNT(*)
----------
32643376
SQL> select /*+ parallel(a,8) */ count(*) from HISTGRM$ a;
COUNT(*)
----------
14531284
SQL> select count(*) from dba_tables;
COUNT(*)
----------
1097
SQL> select count(*) from dba_tab_partitions;
COUNT(*)
----------
5492
SQL> select count(*) from dba_tab_subpartitions;
COUNT(*)
----------
908470
SQL> select count(*) from dba_tables where partitioned='YES';
COUNT(*)
----------
162
SQL> select sum(bytes)/1024/1024 size_mb from dba_data_files where tablespace_name='SYSTEM';
SIZE_MB
----------
10233.9922
SQL> select /*+ parallel(a,8) */ count(*) from hist_head$ a;
COUNT(*)
----------
32643376
SQL> select /*+ parallel(a,8) */ count(*) from HISTGRM$ a;
COUNT(*)
----------
14531284
SQL> select count(*) from dba_tables;
COUNT(*)
----------
1097
SQL> select count(*) from dba_tab_partitions;
COUNT(*)
----------
5492
SQL> select count(*) from dba_tab_subpartitions;
COUNT(*)
----------
908470
SQL> select count(*) from dba_tables where partitioned='YES';
COUNT(*)
----------
162
上面一组数据来源于某个10g的数据库，SYSTE表空间超过了10g，仅仅只有162个分区表，而子分区数达到了90万个。而与列的统计信息有关的再从个数据字典表，一个其行数达到了3200多万行，另一个其行数达到了1400多万行。终其原因，就在于这个系统太多的子分区，而绝大多数子分区是LIST类型分区的，这样收集统计信息时，会收集子分区一级统计信息，这样导致列统计信息占用了大量的SYSTEM表空间，使SYSTEM表空间暴涨。
sys.hist_head$主要存储包括high value和low value的列的统计信息，而sys.histgrm$表存储”column size > 1″时的直方图数据。看看上面提到的hist_head$表，如果一个表，有100个子分区，共有20列，那么在收集了子分区统计信息而收集统计信息时收集了所有列的统计信息的情况下，hist_head$仅包含子分区一级就会有2000行数据。这不难理解上面提到的库，hist_head$为什么会有3200多万行数据。
另外值得注意的是，如果10g开启了recyclebin特性，表只是被DROP，还未被PURGE的情况下，其统计信息仍然存储在数据字典中。
在数据库中有大量子分区时，是否应该子分区的统计信息，和怎么样收集子分区的统计信息，应结合应用情况，需要仔细考虑。应当避免如上面提到的数据库，过多的统计数据导致SYSTEM表空间暴涨。