Oracle中收集表与列统计信息 - 数据库编程

我们在分析某些语句的性能时，会分析一些信息。像表、列、索引、直方图等等，本篇主要讲表与列的统计信息收集与分析。

一、表统计信息

首先创建一个测试表，更新一些数据，加入一些约束：

CREATE TABLE t
AS
SELECT rownum AS id,
       round(dbms_random.normal*1000) AS val1,
       100 + round(ln(rownum/3.25+2)) AS val2,
       100 + round(ln(rownum/3.25+2)) AS val3,
       dbms_random.string('p',250) AS pad
FROM All_Objects
WHERE ROWNUM<=1000
ORDER BY dbms_random.value;

UPDATE T SET VAL1 = NULL WHERE VAL1 < 0;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY(ID);

CREATE INDEX t_val1_i ON t(val1);
CREATE INDEX t_val2_i ON t(val2);

BEGIN
DBMS_STATS.GATHER_TABLE_STATS(OWNNAME          => USER,
                                TABNAME          => 'T',
                                ESTIMATE_PERCENT => 100,
                                METHOD_OPT       => 'for all columns size skewonly',
                                CASCADE          => TRUE);
END;

此时表已经搜集了统计信息，查看表的统计信息用user_tab_statistics。

SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
  FROM USER_TAB_STATISTICS
 WHERE TABLE_NAME = 'T';

NUM_ROWS	BLOCKS	EMPTY_BLOCKS	AVG_SPACE	CHAIN_CNT	AVG_ROW_LEN
1000	44	0	0	0	265

关于每一列的解释联机文档上都有，这里blocks是高水位以下的数据块数，empty_blocks是高水位以上的数据块数。Dbms_stats不计算EMPTY_BLOCKS、AVG_SPACE、CHAIN_CNT。

二、列统计信息

SELECT COLUMN_NAME  AS "NAME",
       NUM_DISTINCT AS "#DST",
       LOW_VALUE,
       HIGH_VALUE,
       DENSITY      AS "DENS",
       NUM_NULLS    AS "#NULL",
       AVG_COL_LEN  AS "AVGLEN",
       HISTOGRAM,
       NUM_BUCKETS
  FROM USER_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'T';

NAME	#DST	LOW_VALUE	HIGH_VALUE	DENS	#NULL	AVGLEN	HISTOGRAM	NUM_BUCKETS
ID	1000	C102	C20B	0.001	0	4	NONE	1
VAL1	450	C102	C22D4B	0.002513356	481	3	HEIGHT BALANCED	234
VAL2	6	C20202	C20207	0.0005	0	4	FREQUENCY	6
VAL3	6	C20202	C20207	0.0005	0	4	FREQUENCY	6
PAD	1000	202523422F3B40542B2D7276252F3C7035494769323F275E773236205A4A2638	7E6664433E4F2C2E515C3570536E7D7B303D546B4D50244C42367A76536F6B6A	0.001	0	251	HEIGHT BALANCED	254

这里几列统计信息解释一下：

NUM_DISTINCT：该列中唯一值的数量。

LOW_VALUE：该列的最小值。显示为内部存储格式。对于字符串列，只存储前32字节。

HIGH_VALUE：该列的最大值。显示为内部存储格式。对于字符串列，只存储前32字节。

DENSITY：0到1之间的一个小数。接近0表示对于该列的过滤操作能去掉大多数的行。接近1表示对于该列的过滤操作起不到什么作用。如果没有直方图，DENSITY=1/NUM_DISTINCT。

HISTOGRAM：表明是否有直方图信息，如果有，是什么类型？FREQUENCY表示频率类型，HEIGHT BALANCED表示平均分布类型；如果没有，则为NONE。

NUM_BUCKETS：直方图里的桶数。它表示一组同类的数值放在一起。直方图最少由一个桶组成。如果没有直方图，则为1，最大桶数为254。

这里的LOW_VALUE和HIGH_VALUE都是内部格式，所以必须转换为可读懂的格式，有两种方法。

①使用工具包utl_raw提供的函数cast_to_binary_double、cast_to_binary_float、cast_to_binary_integer、cast_to_number、cast_to_nvarchar2、cast_to_raw和cast_to_varchar2。这些函数就是把内部存储格式转换为实际值。

SELECT UTL_RAW.CAST_TO_NUMBER(LOW_VALUE) AS LOW_VALUE,
       UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) AS HIGH_VALUE
  FROM USER_TAB_COL_STATISTICS
 WHERE TABLE_NAME = 'T'
   AND COLUMN_NAME = 'VAL1';

LOW_VALUE	HIGH_VALUE
1	4474

②使用dbms_stats提供的过程convert_raw_value、convert_raw_value_nvarchar和convert_raw_value_rowid。该过程不能直接在SQL语句中使用，通常只用于PL/SQL程序中。

-- Created on 2013/12/4 by LENOVO 
DECLARE
  L_LOW_VALUE  USER_TAB_COL_STATISTICS.LOW_VALUE%TYPE;
  L_HIGH_VALUE USER_TAB_COL_STATISTICS.HIGH_VALUE%TYPE;
  L_VAL1       T.VAL1%TYPE;
BEGIN
  SELECT LOW_VALUE, HIGH_VALUE
    INTO L_LOW_VALUE, L_HIGH_VALUE
    FROM USER_TAB_COL_STATISTICS
   WHERE TABLE_NAME = 'T'
     AND COLUMN_NAME = 'VAL1';

  DBMS_STATS.CONVERT_RAW_VALUE(L_LOW_VALUE, L_VAL1);
  DBMS_OUTPUT.PUT_LINE('low_value: ' || L_VAL1);
  DBMS_STATS.CONVERT_RAW_VALUE(L_HIGH_VALUE, L_VAL1);
  DBMS_OUTPUT.PUT_LINE('high_value: ' || L_VAL1);
END;

low_value: 1
high_value: 4474