创建测试表
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 直方图桶数
--转自