1、创建测试表
create table t as
select rownum rn,
object_name,
rpad('*', 32, '*') || object_type object_type
from all_objects
where rownum <= 10000;
select object_type ,count(1) from t group by object_type;
2、收集统计信息
begin
dbms_stats.gather_table_stats(user,
'T',
method_opt => 'FOR COLUMNS object_type SIZE 254');
end;
/
select *
from t
where object_type = '********************************TABLE';
3、查看直方图
select table_name,
column_name,
--endpoint_value,
--endpoint_number,
endpoint_actual_value,
endpoint_number - lag(endpoint_number, 1, 0) over(order by endpoint_number) as frequency
from user_tab_histograms
where table_name = 'T'
--and endpoint_number > 0
--and endpoint_actual_value is not null
order by 1, 2, 4;
4、更新列,收集统计信息,查询直方图
update t set object_type = substr(object_type, 2);
从上面的结果看,oracle直方图的取值,仅取32位。
如果前32位相同,收集直方图后,oracle会认为是相同的,导致生成错误的执行计划。
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/720091/viewspace-1075475/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/720091/viewspace-1075475/