直方图 二
oracle数据库的直方图用了bucket方式来描述目标列的数据分布，它其实是一个逻辑概念，每个bucket就是一组，每个bucket会存储一个或这多个目标列的数据。oracle会用两个纬度来描述一个bucket，endpoint number和endpoint value,这会记录在数据字典的基表里，HISTGRM$,也可以查询数据字典 DBA_TAB_HISTOGRAMS,DBA_PART_HISTOGRAMS,DBA_SUBPART_HISTGRAMS.
同时，oracle还会记录目标列的直方图统计信息所用的bucket的总数，num_buckets字段即目标列的bucket数量。
在oracle12c之前，直方图分为两类，frequency和HEIGHT BALANCED.如果存储在数据字典里，描述目标列直方图的bucket的数量等于目标列distinct值的数量。则这种类型的直方图就是frequency类型的直方图.如果数据字典里面记录的目标列的直方图bucket的数量小于目标列distinct值的数量，则这种类型的直方图就是height balanced类型的直方图。
frequency类型的直方图。
对于frequency类型的直方图，目标列直方图的bucket的数量等于目标列distinct值的数量，目标列有多少个distinct值，数据字典DBA_TAB_HISTOGRAMS,DBA_PART_HISTOGRAMS,DBA_SUBPART_HISTOGRAMS中就会记录多少条记录，每一条记录表示对一个bucket的描述，上述数据字典中的字段，ENDPOINT_VALUE记录了distinct值，而字段ENDPOINT_NUMBER记录了到此distinct值为止总共的记录。这是一个累加值。可以用这条记录的ENDPOINT_NUMBER值减去上一条记录的ENDPOINT_NUMBER值，就得到当前ENDPOINT_VALUE的值。
下面举个列子来详细的讲述frequency类型的直方图。
上述语句，往表里面插入了4万条记录，有10个distinct值，并且不是均匀分布的。
收集统计信息的时候，指定对列 x收集直方图。
发现直方图的信息为none。
这是因为，oracle只对在where条件后出现的列才会收集直方图。列是否使用可以用以下语句检测：
执行下这个语句
select * from h where x = 1；
然后执行下列语句
再次收集统计信息。
其实上述结果可以用下面语句统计出来：
上面是针对数字类型的直方图，如果针对文本类型的直方图，oracle是怎么记录和描述的。
在oracle数据库里面，如果针对文本型的字段收集直方图统计信息，则oracle会将该文本型字段的文本值的头32个字节（byte）给取出来，（实际上只 取头15个字节），并将其转换为一个浮点数，然后就将这个浮点数做为其直方图统计信息存储在上述数据字典中。
select count(*) from t1 where b = '1'
让数据字典里面记载该列已经使用的信息。
SELECT * FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T1' AND COLUMN_NAME = 'B'
SELECT * FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T1' AND COLUMN_NAME = 'B';
字符1 和 字符2 对应的16进制（ascii码）如下：
讲0x31，0x32一直右补齐0一直到15个字节。
SELECT TO_NUMBER('310000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') FROM DUAL
UNION ALL
SELECT TO_NUMBER('320000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') FROM DUAL
针对文本型的字段收集直方图统计信息，想象这样一个场景，对于哪些超过32个字节的文本型字段，只要前32个字节相同，oracle在收集直方图统计信息时就会认为这些记录在该字段的文本值是相同的。即使他们不相同，这种先天性的缺陷会直接影响cbo对相关文本字段的可选择率，即返回结果集的Cardinality的评估。
下面的例子可以说明这一点
select b,length(b) from t2
SELECT * FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T2' AND COLUMN_NAME = 'B';
明明是2个distinct值，怎么只有一个呢。就是上面所说的原因
select dump('a',16) from dual
select to_number('616161616161616161616161616161','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual
我们看看对基数的影响
select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'
发现基数为3，这和直方图记录的一样。
本节中讲述了frenquency类型的直方图，下节主要讲述height balanced类型的直方图。
同时，oracle还会记录目标列的直方图统计信息所用的bucket的总数，num_buckets字段即目标列的bucket数量。
在oracle12c之前，直方图分为两类，frequency和HEIGHT BALANCED.如果存储在数据字典里，描述目标列直方图的bucket的数量等于目标列distinct值的数量。则这种类型的直方图就是frequency类型的直方图.如果数据字典里面记录的目标列的直方图bucket的数量小于目标列distinct值的数量，则这种类型的直方图就是height balanced类型的直方图。
frequency类型的直方图。
对于frequency类型的直方图，目标列直方图的bucket的数量等于目标列distinct值的数量，目标列有多少个distinct值，数据字典DBA_TAB_HISTOGRAMS,DBA_PART_HISTOGRAMS,DBA_SUBPART_HISTOGRAMS中就会记录多少条记录，每一条记录表示对一个bucket的描述，上述数据字典中的字段，ENDPOINT_VALUE记录了distinct值，而字段ENDPOINT_NUMBER记录了到此distinct值为止总共的记录。这是一个累加值。可以用这条记录的ENDPOINT_NUMBER值减去上一条记录的ENDPOINT_NUMBER值，就得到当前ENDPOINT_VALUE的值。
下面举个列子来详细的讲述frequency类型的直方图。
点击(此处)折叠或打开
-
create table h (x number);
-
-
-
declare
-
i number;
-
begin
-
for i in 1..3296 loop
-
insert into h values(1);
-
end loop;
-
for i in 1..100 loop
-
insert into h values(3);
-
end loop;
-
for i in 1..798 loop
-
insert into h values(5);
-
end loop;
-
for i in 1..3970 loop
-
insert into h values(7);
-
end loop;
-
for i in 1..16293 loop
-
insert into h values(10);
-
end loop;
-
for i in 1..3399 loop
-
insert into h values(16);
-
end loop;
-
for i in 1..3651 loop
-
insert into h values(27);
-
end loop;
-
for i in 1..3892 loop
-
insert into h values(32);
-
end loop;
-
for i in 1..3521 loop
-
insert into h values (39);
-
end loop;
-
for i in 1..1080 loop
-
insert into h values(49);
-
end loop;
-
commit;
-
end;
-
-
-
-
-
begin
-
dbms_stats.gather_table_stats('MEPF_DEV',
-
'h',
-
estimate_percent => 100,
-
method_opt => 'for columns size auto X',cascade => true);
- end;
上述语句，往表里面插入了4万条记录，有10个distinct值，并且不是均匀分布的。
收集统计信息的时候，指定对列 x收集直方图。
发现直方图的信息为none。
这是因为，oracle只对在where条件后出现的列才会收集直方图。列是否使用可以用以下语句检测：
执行下这个语句
select * from h where x = 1；
然后执行下列语句
点击(此处)折叠或打开
-
select object_id from dba_objects where object_name='H';
-
-
-
select name,intcol# from sys.col$ where obj#=79546;
-
- select obj#,intcol#,equality_preds from sys.col_usage$ where obj#=79546;
再次收集统计信息。
点击(此处)折叠或打开
- select * from dba_tab_histograms t where t.TABLE_NAME = 'H'
其实上述结果可以用下面语句统计出来：
点击(此处)折叠或打开
- SELECT X, COUNT(*), SUM(COUNT(*)) OVER(ORDER BY X) FROM H GROUP BY X;
上面是针对数字类型的直方图，如果针对文本类型的直方图，oracle是怎么记录和描述的。
在oracle数据库里面，如果针对文本型的字段收集直方图统计信息，则oracle会将该文本型字段的文本值的头32个字节（byte）给取出来，（实际上只 取头15个字节），并将其转换为一个浮点数，然后就将这个浮点数做为其直方图统计信息存储在上述数据字典中。
点击(此处)折叠或打开
- SELECT B,COUNT(*) FROM T1 GROUP BY B
select count(*) from t1 where b = '1'
让数据字典里面记载该列已经使用的信息。
点击(此处)折叠或打开
-
begin
-
dbms_stats.gather_table_stats('MEPF_DEV',
-
'T1',
-
estimate_percent => 100,
-
method_opt => 'for columns size auto B',cascade => true);
- end;
SELECT * FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T1' AND COLUMN_NAME = 'B';
字符1 和 字符2 对应的16进制（ascii码）如下：
点击(此处)折叠或打开
-
SELECT DUMP('1',16) FROM DUAL
-
UNION ALL
-
SELECT DUMP('2',16) FROM DUAL
讲0x31，0x32一直右补齐0一直到15个字节。
SELECT TO_NUMBER('310000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') FROM DUAL
UNION ALL
SELECT TO_NUMBER('320000000000000000000000000000','XXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') FROM DUAL
针对文本型的字段收集直方图统计信息，想象这样一个场景，对于哪些超过32个字节的文本型字段，只要前32个字节相同，oracle在收集直方图统计信息时就会认为这些记录在该字段的文本值是相同的。即使他们不相同，这种先天性的缺陷会直接影响cbo对相关文本字段的可选择率，即返回结果集的Cardinality的评估。
下面的例子可以说明这一点
点击(此处)折叠或打开
-
create table t2 (b varchar2(33));
-
-
-
insert into t2 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1');
-
insert into t2 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa2');
- insert into t2 values ('aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa3');
点击(此处)折叠或打开
-
select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'
-
-
-
-
begin
-
dbms_stats.gather_table_stats('MEPF_DEV',
-
'T2',
-
estimate_percent => 100,
-
method_opt => 'for columns size auto B',cascade => true);
- end;
SELECT * FROM DBA_TAB_HISTOGRAMS WHERE TABLE_NAME = 'T2' AND COLUMN_NAME = 'B';
明明是2个distinct值，怎么只有一个呢。就是上面所说的原因
select dump('a',16) from dual
select to_number('616161616161616161616161616161','xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx') from dual
我们看看对基数的影响
select count(*) from t2 where b='aaaaaaaaaaaaaaaaaaaaaaaaaaaaaaaa1'
发现基数为3，这和直方图记录的一样。
本节中讲述了frenquency类型的直方图，下节主要讲述height balanced类型的直方图。
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/30109892/viewspace-1834476/ ，如需转载，请注明出处，否则将追究法律责任。
请登录后发表评论
登录
全部评论
