oracle 表统计信息

创建测试表
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 BY dbms_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 FROM user_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 FROM user_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 FROM user_tab_col_statistics
11 WHERE table_name = 'T';

NAME #DST LOW_VALUE HIGH_VALUE DENS #NULL AVGLEN HISTOGRAM #BKT
---- ----- -------------- -------------- ------- ----- ------ --------------- -----
ID 1000 C102 C20B .00100 0 4 NONE 1
VAL1 444 C105 C22123 .00241 488 3 HEIGHT BALANCED 254
VAL2 6 C20202 C20207 .00050 0 4 FREQUENCY 6
VAL3 6 C20202 C20207 .00050 0 4 FREQUENCY 6
PAD 1000 202467387A6D55 7E71426D7E7C6D .00100 0 251 HEIGHT BALANCED 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 直方图桶数