GATHER_TABLE_STATS
收集表、列和索引统计数据。
SQL> execute dbms_stats.gather_table_stats(-
>ownname => 'test',-
>tabname => 'PARTTAB',-
>partname => null,- --> 收集所有分区状态
>estimate_percent => null,- --> 计算模式
>block_sample => false,- --> 默认值，计算模式下无意义
>method_opt => 'FOR ALL COLUMNS SIZE 1',- --> 表和列统计，不生成直方图
>degree => null,- --> 基于PARTTAB表上的DOP设置的默认并行度
>granularity => 'default',- -->收集全局和分区统计数据
>cascade => true ,- --> 产生所有统计数据
>stattab => null,- -->统计数据将被存储在字典中
>statid => null,-
>statown => null);
>ownname => 'test',-
>tabname => 'PARTTAB',-
>partname => null,- --> 收集所有分区状态
>estimate_percent => null,- --> 计算模式
>block_sample => false,- --> 默认值，计算模式下无意义
>method_opt => 'FOR ALL COLUMNS SIZE 1',- --> 表和列统计，不生成直方图
>degree => null,- --> 基于PARTTAB表上的DOP设置的默认并行度
>granularity => 'default',- -->收集全局和分区统计数据
>cascade => true ,- --> 产生所有统计数据
>stattab => null,- -->统计数据将被存储在字典中
>statid => null,-
>statown => null);
默认情况下，如果CASCADE=>TRUE，不会计算索引统计。
SQL> select table_name, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN, GLOBAL_STATS, USER_STATS, sample_size from user_tables
where table_name = 'PARTTAB';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE
---------- -------- ------ ------------ --------- ----------- ------------ ---------- -----------
PARTTAB 400 8 0 0 11 YES NO 400
where table_name = 'PARTTAB';
TABLE_NAME NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE
---------- -------- ------ ------------ --------- ----------- ------------ ---------- -----------
PARTTAB 400 8 0 0 11 YES NO 400
至此，统计数据就被更新了。GLOBAL_STATS列也被初始化了。
SQL> select partition_name "Partition", NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, AVG_ROW_LEN,
SAMPLE_SIZE, global_stats, user_stats
from user_tab_partitions
where table_name = 'PARTTAB'
order by partition_position
/
Partition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE
---------- -------- ------ ------------ ---------- ----------- ------------ ---------- -----------
Q1 100 2 0 0 11 YES NO 100
Q2 100 2 0 0 11 YES NO 100
Q3 100 2 0 0 11 YES NO 100
Q4 100 2 0 0 11 YES NO 100
SAMPLE_SIZE, global_stats, user_stats
from user_tab_partitions
where table_name = 'PARTTAB'
order by partition_position
/
Partition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN GLOBAL_STATS USER_STATS SAMPLE_SIZE
---------- -------- ------ ------------ ---------- ----------- ------------ ---------- -----------
Q1 100 2 0 0 11 YES NO 100
Q2 100 2 0 0 11 YES NO 100
Q3 100 2 0 0 11 YES NO 100
Q4 100 2 0 0 11 YES NO 100
GLOBAL_STATS统计数据在表级再次被收集。
SQL> select partition_name \"Partition\", subpartition_name \"Subpartition\", NUM_ROWS, BLOCKS, EMPTY_BLOCKS
SAMPLE_SIZE, global_stats, user_stats
from user_tab_subpartitions
where table_name = 'PARTTAB'
order by partition_name, subpartition_position
/
Partition Subpartition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE GLOBAL_STATS USER_STATS
---------- ----------- -------- ------ ------------ --------- ----------- ----------- ------------ ----------
Q1 SYS_SUBP10365 NO NO
Q1 SYS_SUBP10366 NO NO
Q2 SYS_SUBP10367 NO NO
Q2 SYS_SUBP10368 NO NO
Q3 SYS_SUBP10369 NO NO
Q3 SYS_SUBP10370 NO NO
Q4 SYS_SUBP10371 NO NO
Q4 SYS_SUBP10372 NO NO
SAMPLE_SIZE, global_stats, user_stats
from user_tab_subpartitions
where table_name = 'PARTTAB'
order by partition_name, subpartition_position
/
Partition Subpartition NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE AVG_ROW_LEN SAMPLE_SIZE GLOBAL_STATS USER_STATS
---------- ----------- -------- ------ ------------ --------- ----------- ----------- ------------ ----------
Q1 SYS_SUBP10365 NO NO
Q1 SYS_SUBP10366 NO NO
Q2 SYS_SUBP10367 NO NO
Q2 SYS_SUBP10368 NO NO
Q3 SYS_SUBP10369 NO NO
Q3 SYS_SUBP10370 NO NO
Q4 SYS_SUBP10371 NO NO
Q4 SYS_SUBP10372 NO NO
子分区级的统计数据没有被计算进去。
SQL>select COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from
user_tab_col_statistics where table_name = 'PARTTAB'
/
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
----------- ------------ ------- --------- ----------- -------------
ORDID 0 0 400 1 12-DEC-02
PARTCOL 4 .25 0 1 12-DEC-02
DETAILS 100 .01 0 1 12-DEC-02
AMOUNT 0 0 400 1 12-DEC-02
user_tab_col_statistics where table_name = 'PARTTAB'
/
COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
----------- ------------ ------- --------- ----------- -------------
ORDID 0 0 400 1 12-DEC-02
PARTCOL 4 .25 0 1 12-DEC-02
DETAILS 100 .01 0 1 12-DEC-02
AMOUNT 0 0 400 1 12-DEC-02
因为这里没有直方图，所有NUM_BUCKETS的值是1，但列统计被初始化了。
下面显示的是每个分区列相同的结果：
SQL>select partition_name, COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS, NUM_BUCKETS, LAST_ANALYZED from user_part_col_statistics
where table_name = 'PARTTAB'
/
PARTITION_ COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
---------- ---------- ------------ ---------- ---------- ------- -----------
Q1 ORDID 0 0 100 1 12-DEC-07
Q1 PARTCOL 1 1 0 1 12-DEC-07
Q1 DETAILS 100 .01 0 1 12-DEC-07
Q1 AMOUNT 0 0 100 1 12-DEC-07
Q2 ORDID 0 0 100 1 12-DEC-07
Q2 PARTCOL 1 1 0 1 12-DEC-07
Q2 DETAILS 100 .01 0 1 12-DEC-07
Q2 AMOUNT 0 0 100 1 12-DEC-07
Q3 ORDID 0 0 100 1 12-DEC-07
Q3 PARTCOL 1 1 0 1 12-DEC-07
Q3 DETAILS 100 .01 0 1 12-DEC-07
Q3 AMOUNT 0 0 100 1 12-DEC-07
Q4 ORDID 0 0 100 1 12-DEC-07
Q4 PARTCOL 1 1 0 1 12-DEC-07
Q4 DETAILS 100 .01 0 1 12-DEC-07
Q4 AMOUNT 0 0 100 1 12-DEC-07
where table_name = 'PARTTAB'
/
PARTITION_ COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS LAST_ANALYZED
---------- ---------- ------------ ---------- ---------- ------- -----------
Q1 ORDID 0 0 100 1 12-DEC-07
Q1 PARTCOL 1 1 0 1 12-DEC-07
Q1 DETAILS 100 .01 0 1 12-DEC-07
Q1 AMOUNT 0 0 100 1 12-DEC-07
Q2 ORDID 0 0 100 1 12-DEC-07
Q2 PARTCOL 1 1 0 1 12-DEC-07
Q2 DETAILS 100 .01 0 1 12-DEC-07
Q2 AMOUNT 0 0 100 1 12-DEC-07
Q3 ORDID 0 0 100 1 12-DEC-07
Q3 PARTCOL 1 1 0 1 12-DEC-07
Q3 DETAILS 100 .01 0 1 12-DEC-07
Q3 AMOUNT 0 0 100 1 12-DEC-07
Q4 ORDID 0 0 100 1 12-DEC-07
Q4 PARTCOL 1 1 0 1 12-DEC-07
Q4 DETAILS 100 .01 0 1 12-DEC-07
Q4 AMOUNT 0 0 100 1 12-DEC-07
下面显示的是PARTTAB表的子分区装入的统计数据：
SQL> select subpartition_name \"Subpartition\", COLUMN_NAME, NUM_DISTINCT, DENSITY, NUM_NULLS,
NUM_BUCKETS from dba_subpart_col_statistics where table_name = 'PARTTAB'
order by column_name
/
Subpartition COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS
------------ ----------- ------------ ------- --------- -----------
SYS_SUBP10365 PARTCOL
SYS_SUBP10365 ORDID
SYS_SUBP10365 DETAILS
SYS_SUBP10365 AMOUNT
SYS_SUBP10366 PARTCOL
SYS_SUBP10366 ORDID
SYS_SUBP10366 DETAILS
SYS_SUBP10366 AMOUNT
SYS_SUBP10367 PARTCOL
SYS_SUBP10367 ORDID
SYS_SUBP10367 DETAILS
SYS_SUBP10367 AMOUNT
SYS_SUBP10368 PARTCOL
SYS_SUBP10368 ORDID
SYS_SUBP10368 DETAILS
SYS_SUBP10368 AMOUNT
SYS_SUBP10369 PARTCOL
SYS_SUBP10369 ORDID
SYS_SUBP10369 DETAILS
SYS_SUBP10369 AMOUNT
SYS_SUBP10370 PARTCOL
SYS_SUBP10370 ORDID
SYS_SUBP10370 DETAILS
SYS_SUBP10370 AMOUNT
SYS_SUBP10371 PARTCOL
SYS_SUBP10371 ORDID
SYS_SUBP10371 DETAILS
SYS_SUBP10371 AMOUNT
SYS_SUBP10372 PARTCOL
SYS_SUBP10372 ORDID
SYS_SUBP10372 DETAILS
SYS_SUBP10372 AMOUNT
NUM_BUCKETS from dba_subpart_col_statistics where table_name = 'PARTTAB'
order by column_name
/
Subpartition COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS NUM_BUCKETS
------------ ----------- ------------ ------- --------- -----------
SYS_SUBP10365 PARTCOL
SYS_SUBP10365 ORDID
SYS_SUBP10365 DETAILS
SYS_SUBP10365 AMOUNT
SYS_SUBP10366 PARTCOL
SYS_SUBP10366 ORDID
SYS_SUBP10366 DETAILS
SYS_SUBP10366 AMOUNT
SYS_SUBP10367 PARTCOL
SYS_SUBP10367 ORDID
SYS_SUBP10367 DETAILS
SYS_SUBP10367 AMOUNT
SYS_SUBP10368 PARTCOL
SYS_SUBP10368 ORDID
SYS_SUBP10368 DETAILS
SYS_SUBP10368 AMOUNT
SYS_SUBP10369 PARTCOL
SYS_SUBP10369 ORDID
SYS_SUBP10369 DETAILS
SYS_SUBP10369 AMOUNT
SYS_SUBP10370 PARTCOL
SYS_SUBP10370 ORDID
SYS_SUBP10370 DETAILS
SYS_SUBP10370 AMOUNT
SYS_SUBP10371 PARTCOL
SYS_SUBP10371 ORDID
SYS_SUBP10371 DETAILS
SYS_SUBP10371 AMOUNT
SYS_SUBP10372 PARTCOL
SYS_SUBP10372 ORDID
SYS_SUBP10372 DETAILS
SYS_SUBP10372 AMOUNT
子分区列上没有装入统计数据。
这里的分区对象包括了不止一套统计数据，这是因为统计数据既可以为整个对象产生，也可以为分区和子分区产生。
