Oracle的TPCC测试

根据Jonathan Lewis老先生的测试实例，发觉cluster 的sort功能，完全是为了tpcc测试而存在的，非等值的查询语句，都会走错误的计划，得出错误的结果。

这回oracle麻烦大了...

[sql] 
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';