oracle表统计信息

原文链接：http://likgrj.blog.163.com/blog/static/1143818642012551150508/

创建测试表
SQL> CREATE TABLE t
AS
 2   3  SELECT rownum AS id,
 4        round(dbms_random.normal*1000) AS val1,
 5        100+round(ln(rownum/3.25+2)) AS val2,
 6        100+round(ln(rownum/3.25+2)) AS val3,
 7        dbms_random.string('p',250) AS pad
  8  FROM dual
  9  CONNECT BY level <=1000
 10  ORDER BYdbms_random.value;
 
 
Table created.

SQL> UPDATE t SET val1 = NULL WHERE val1 < 0;

488 rows updated.

SQL> ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

Table altered.

SQL> CREATE INDEX t_val1_i ON t (val1);
SQL> CREATE INDEX t_val2_i ON t (val2);
Index created.

查看统计信息为空
SQL> SELECT num_rows, blocks, empty_blocks, avg_space,chain_cnt, avg_row_len
  2  FROMuser_tab_statistics
  3  WHERE table_name = 'T';

 NUM_ROWS    BLOCKS EMPTY_BLOCKS  AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------------------

收集统计信息看看
SQL> BEGIN
 2   dbms_stats.gather_table_stats(ownname         => user,
 3                                 tabname         => 'T',
                               estimate_percent => 100,
                               method_opt      => 'for all columns size skewonly',
                               cascade         => TRUE);
END;
/

可以查到表统计信息了
SQL> SELECT num_rows, blocks, empty_blocks, avg_space,chain_cnt, avg_row_len
  2  FROMuser_tab_statistics
  3  WHERE table_name = 'T';

 NUM_ROWS    BLOCKS EMPTY_BLOCKS  AVG_SPACE CHAIN_CNT AVG_ROW_LEN
---------- ---------- ------------ ---------- ---------------------
     1000        44           0         0         0        265
字段说明：
NUM_ROWS记录行数   
BLOCKS  高水位线以下的数据块数
EMPTY_BLOCKS  高水位线以上数据块数，dbms_stat不统计.
AVG_SPACE  平均空闲空间，不统计
CHAIN_CNT  行迁移行数，不统计
AVG_ROW_LEN 行平均长度
    
再看看列统计信息  
SQL> SELECT column_name AS "NAME", 
 2        num_distinct AS "#DST", 
 3        low_value, 
 4        high_value, 
 5        density AS "DENS", 
 6        num_nulls AS "#NULL", 
 7        avg_col_len AS "AVGLEN", 
 8        histogram, 
 9        num_buckets AS "#BKT"
 10  FROMuser_tab_col_statistics
 11  WHERE table_name = 'T';

NAME  #DSTLOW_VALUE     HIGH_VALUE       DENS #NULL AVGLENHISTOGRAM       #BKT
---- ----- -------------- -------------- ------- ----- --------------------- -----
ID    1000C102          C20B           .00100    0     4NONE               1
VAL1   444C105          C22123         .00241  488     3 HEIGHT BALANCED   254
VAL2    6C20202        C20207         .00050    0     4FREQUENCY          6
VAL3    6C20202        C20207         .00050    0     4FREQUENCY          6
PAD   1000 202467387A6D557E71426D7E7C6D .00100    0    251 HEIGHTBALANCED   254
          6F682A6D794360 22537B51587E55
          797C3525686D3E 33373C25475C48
          5D672D21453752 2A4F322D31414F
          3E39393F      47786E27

统计信息说明
column_name 字段名
num_distinct 唯一值数量 
low_value 该列最小值
high_value 该列最大值
density 表示该列数据的重复率，0到1的小数，越接近0表示重复率越低。
num_nulls 该列null值的数量
avg_col_len 列平均长度，以字节为单位
histogram 是否有直方图统计信息，none：没有，frequency：频率直方图，height balanced等高直方图。
num_buckets 直方图桶数