一直想知道low_value和high_value是如何计算出来的，最近分析了dbms_stats.gather_table_statsr的部份操作，总结如下：
Num_distinct: count(distint col1)
Num_nulls: count(*) -count(col1)
Low_value: substrb(dump(min(col1),16,0,32),1,120)--取冒号后面的数据并将逗号替换掉
High_value:substrb(dump(max(col1),16,0,32,1,120) --取冒号后面的数据并将逗号替换掉
Density:1/count(distinct col1)
Avg_col_len:sum(vsize(col1))/count(col1)--实际计算出来的结果为向上取整再加1，为什么要这样做不太清楚
示例：
17:15:13 test@REG>create table t2 as
17:15:21 2select rownum id,
17:15:21 3object_name,
17:15:21 4rpad('x',100) rpadding
17:15:21 5from dba_objects
17:15:21 6where rownum UPDATE T2 SET RPADDING='' WHERE ROWNUM commit;
提交完成。
已用时间:00: 00: 00.01
17:17:59 test@REG>exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL 过程已成功完成。
17:18:28 test@REG>select A.column_name, NUM_DISTINCT,NUM_NULLS,LOW_VALUE,HIGH_VALUE,DENSITY,AVG_COL_LEN
17:22:44 2from user_tab_columns a
17:22:48 3where a.TABLE_NAME='T2';
COLUMN_NAMENUM_DISTINCT NUM_NULLS LOW_VALUE
HIGH_VALUE
DENSITY AVG_COL_LEN
------------ ------------ --------- ------------------------------ ------------------------------ ---------- -----------
ID
100 0 C102
C202
.01 3
OBJECT_NAME 100 0 2F31303035626433305F4C6E6B6443 2F31336236353338315F4163636573.01
29
6F6E7374616E74
7369626C655374617465
RPADDING
1 9 782020202020202020202020202020 782020202020202020202020202020
1
92
202020202020202020202020202020 202020202020202020202020202020
2020
2020
已用时间:00: 00: 00.09
17:33:37 test@REG>select 'id' column_name,
17:33:40 2count(distinct id) NUM_DISTINCT,
17:33:40 3count(1) - count(id) NUM_NULLS,
17:33:40 4substrb(dump(min(id),16,0,32),1,120) LOW_VALUE,
17:33:40 5substrb(dump(max(id),16,0,32),1,120)HIGH_VALUE,
17:33:40 61/count(distinct id) DENSITY,
17:33:40 7ceil(sum(vsize(id))/count(*)) + 1 AVG_COL_LEN
17:33:40 8from t2
17:33:40 9union all
17:33:4010select 'object_name' column_name,
17:33:4011count(distinct object_name) NUM_DISTINCT,
17:33:4012count(1) - count(object_name) NUM_NULLS,
17:33:4013substrb(dump(min(object_name),16,0,32),1,120) LOW_VALUE,
17:33:4014substrb(dump(max(object_name),16,0,32),1,120)HIGH_VALUE,
17:33:40151/count(distinct object_name) DENSITY,
17:33:4016ceil(sum(vsize(object_name))/count(*)) + 1 AVG_COL_LEN
17:33:4017from t2
17:33:4018union all
17:33:4019select 'RPADDING' column_name,
17:33:4020count(distinct RPADDING) NUM_DISTINCT,
17:33:4021count(1) - count(RPADDING) NUM_NULLS,
17:33:4022substrb(dump(min(RPADDING),16,0,32),1,120) LOW_VALUE,
17:33:4023substrb(dump(max(RPADDING),16,0,32),1,120)HIGH_VALUE,
17:33:40241/count(distinct RPADDING) DENSITY,
17:33:4025ceil(sum(vsize(RPADDING))/count(*)) + 1 AVG_COL_LEN
17:33:4026from t2;
COLUMN_NAME NUM_DISTINCT NUM_NULLS LOW_VALUE
HIGH_VALUE
DENSITY AVG_COL_LEN
-----------
------------ --------- ------------------------------ ------------------------------ ---------- -----------
id
100 0 Typ=2 Len=2: c1,2
Typ=2 Len=2: c2,2
.01 3
object_name
100 0 Typ=1 Len=22: 2f,31,30,30,35,6 Typ=1 Len=25: 2f,31,33,62,36,3.01
29
2,64,33,30,5f,4c,6e,6b,64,43,6 5,33,38,31,5f,41,63,63,65,73,7
f,6e,73,74,61,6e,74
3,69,62,6c,65,53,74,61,74,65
RPADDING
1 9 Typ=1 Len=100: 78,20,20,20,20, Typ=1 Len=100: 78,20,20,20,20,
1
92
20,20,20,20,20,20,20,20,20,20, 20,20,20,20,20,20,20,20,20,20,
20,20,20,20,20,20,20,20,20,20, 20,20,20,20,20,20,20,20,20,20,
20,20,20,20,20,20,20 20,20,20,20,20,20,20 |
