统计信息 - 什么是统计信息 - 《SQL优化入门》

前面提到，只有大表才会产生性能问题，那么怎么才能让优化器知道某个表多大呢？这就需要对表收集统计信息。我们在第一章提到的基数、直方图、集群因子等概念都需要事先收集统计信息才能得到。

统计信息类似于战争中的侦察兵，如果情报工作没有做好，打仗就会输掉战争。同样的道理，如果没有正确地收集表的统计信息，或者没有及时地更新表的统计信息，SQL 的执行计划就会跑偏，SQL 也就会出现性能问题。收集统计信息是为了让优化器选择最佳执行计划，以最少的代价（成本）查询出表中的数据。

统计信息主要分为表的统计信息、列的统计信息、索引的统计信息、系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息。

关于系统的统计信息、数据字典的统计信息以及动态性能视图基表的统计信息本书不做讨论，本书重点讨论表的统计信息、列的统计信息以及索引的统计信息。

表的统计信息主要包含表的总行数（num_rows）、表的块数（blocks）以及行平均长度（avg_row_len），我们可以通过查询数据字典 DBA_TABLES 获取表的统计信息。

现在我们创建一个测试表 T_STATS。

create table t_stats as select * from dba_objects;

Table created.

我们查看表 T_STATS 常用的表的统计信息。

 select owner, table_name, num_rows, blocks, avg_row_len
   from dba_tables
   where owner = 'SCOTT'
     and table_name = 'T_STATS';

OWNER           TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- --------------- ---------- ---------- -----------
SCOTT           T_STATS

因为 T_STATS 是新创建的表，没有收集过统计信息，所以从 DBA_TABLES 查询数据是空的。

现在我们来收集表 T_STATS 的统计信息。

BEGIN
    DBMS_STATS.GATHER_TABLE_STATS(ownname          => 'SCOTT',
                                  tabname          => 'T_STATS',
                                  estimate_percent => 100,
                                  method_opt       => 'for all columns size auto',
                                  no_invalidate    => FALSE,
                                 degree           => 1,
                                  cascade          => TRUE);
  END;

PL/SQL procedure successfully completed.

我们再次查看表的统计信息。

select owner, table_name, num_rows, blocks, avg_row_len
   from dba_tables
   where owner = 'SCOTT'
     and table_name = 'T_STATS';

OWNER           TABLE_NAME        NUM_ROWS     BLOCKS AVG_ROW_LEN
--------------- --------------- ---------- ---------- -----------
SCOTT           T_STATS              72674       1061          97

从查询中我们可以看到，表 T_STATS 一共有 72 674 行数据，1 061 个数据块，平均行长度为 97 字节。

列的统计信息主要包含列的基数、列中的空值数量以及列的数据分布情况（直方图）。我们可以通过数据字典 DBA_TAB_COL_STATISTICS 查看列的统计信息。

现在我们查看表 T_STATS 常用的列统计信息。

 select column_name, num_distinct, num_nulls, num_buckets, histogram
   from dba_tab_col_statistics
   where owner = 'SCOTT'
     and table_name = 'T_STATS';

COLUMN_NAME     NUM_DISTINCT  NUM_NULLS NUM_BUCKETS HISTOGRAM
--------------- ------------ ---------- ----------- --------------------
EDITION_NAME               0      72674           0 NONE
NAMESPACE                 21          1           1 NONE
SECONDARY                  2          0           1 NONE
GENERATED                  2          0           1 NONE
TEMPORARY                  2          0           1 NONE
STATUS                     2          0           1 NONE
TIMESTAMP               1592          1           1 NONE
LAST_DDL_TIME           1521          1           1 NONE
CREATED                 1472          0           1 NONE
OBJECT_TYPE               45          0           1 NONE
DATA_OBJECT_ID          7796      64833           1 NONE
OBJECT_ID              72673          1           1 NONE
SUBOBJECT_NAME           140      72145           1 NONE
OBJECT_NAME            44333          0           1 NONE
OWNER                     31          0           1 NONE
15 rows selected.

上面查询中，第一个列表示列名字，第二个列表示列的基数，第三个列表示列中 NULL 值的数量，第四个列表示直方图的桶数，最后一个列表示直方图类型。

在工作中，我们经常使用下面脚本查看表和列的统计信息。

select a.column_name,
         b.num_rows,
        a.num_nulls,
        a.num_distinct Cardinality,
        round(a.num_distinct / b.num_rows * 100, 2) selectivity,
        a.histogram,
        a.num_buckets
    from dba_tab_col_statistics a, dba_tables b
   where a.owner = b.owner
     and a.table_name = b.table_name
     and a.owner = 'SCOTT'
     and a.table_name = 'T_STATS';

COLUMN_NAME       NUM_ROWS  NUM_NULLS CARDINALITY SELECTIVITY HISTOGRAM   NUM_BUCKETS
--------------- ---------- ---------- ----------- ----------- --------------- -------
EDITION_NAME         72674      72674           0           0 NONE                  0
NAMESPACE            72674          1          21         .03 NONE                  1
SECONDARY            72674          0           2           0 NONE                  1
GENERATED            72674          0           2           0 NONE                  1
TEMPORARY            72674          0           2           0 NONE                  1
STATUS               72674          0           2           0 NONE                  1
TIMESTAMP            72674          1        1592        2.19 NONE                  1
LAST_DDL_TIME        72674          1        1521        2.09 NONE                  1
CREATED              72674          0        1472        2.03 NONE                  1
OBJECT_TYPE          72674          0          45         .06 NONE                  1
DATA_OBJECT_ID       72674      64833        7796       10.73 NONE                  1
OBJECT_ID            72674          1       72673         100 NONE                  1
SUBOBJECT_NAME       72674      72145         140         .19 NONE                  1
OBJECT_NAME          72674          0       44333          61 NONE                  1
OWNER                72674          0          31         .04 NONE                  1
15 rows selected.

索引的统计信息主要包含索引 blevel（索引高度-1）、叶子块的个数（leaf_blocks）以及集群因子（clustering_factor）。我们可以通过数据字典 DBA_INDEXES 查看索引的统计信息。

我们在 OBJECT_ID 列上创建一个索引。

create index idx_t_stats_id on t_stats(object_id);

Index created.

创建索引的时候会自动收集索引的统计信息，运行下面脚本查看索引的统计信息。

select blevel, leaf_blocks, clustering_factor,status
    from dba_indexes
   where owner = 'SCOTT'
     and index_name = 'IDX_T_STATS_ID';

    BLEVEL LEAF_BLOCKS CLUSTERING_FACTOR STATUS
---------- ----------- ----------------- ----------------
         1         161              1127 VALID

如果要单独对索引收集统计信息，可以使用下面脚本收集。

 BEGIN
    DBMS_STATS.GATHER_INDEX_STATS(ownname => 'SCOTT',
                                  indname => 'IDX_T_STATS_ID');
  END;

PL/SQL procedure successfully completed.

在本书第 6 章中，我们会详细介绍表的统计信息、列的统计信息以及索引的统计信息是如何被应用于成本计算的。