http://www.oaktable.net/content/sorted-hash-clusters-rip
根据Jonathan Lewis老先生的测试实例,发觉cluster 的sort功能,完全是为了tpcc测试而存在的,非等值的查询语句,都会走错误的计划,得出错误的结果。
这回oracle麻烦大了...
execute dbms_random.seed(0)
create cluster sorted_hash_cluster (
hash_value number(6,0),
sort_value varchar2(2) sort
)
size 300
hashkeys 100
;
create table sorted_hash_table (
hash_value number(6,0),
sort_value varchar2(2),
v1 varchar2(10),
padding varchar2(30)
)
cluster sorted_hash_cluster (
hash_value, sort_value
)
;
begin
for i in 1..5000 loop
insert into sorted_hash_table values(
trunc(dbms_random.value(0,99)),
dbms_random.string('U',2),
lpad(i,10),
rpad('x',30,'x')
);
commit;
end loop;
end;
/
begin
dbms_stats.gather_table_stats(
ownname => user,
tabname =>'sorted_hash_table'
);
end;
/
select count(*) from sorted_hash_table where hash_value = 92;
select count(*) from sorted_hash_table where hash_value = 92 and sort_value is null;
select count(*) from sorted_hash_table where hash_value = 92 and sort_value is not null;
select * from sorted_hash_table where hash_value = 92 and sort_value >= 'YR';
select * from sorted_hash_table where hash_value = 92 and sort_value > 'YR';
oracle这3000W的数值,原来是这么出来的...
http://www.tpc.org/tpcc/results/tpcc_perf_results.asp