Oracle的TPCC测试，原来也是个作弊的东西...

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