- 论坛徽章:
- 0
|
下面的oracle测试语句怎样修改才能成为标准的SQL语句,可以在其他dbms,如db2,sql server,mysql上执行
好像number必须改为decimal,但是不知道sys_guid(),from dual connect by level<=1500000怎么改?
以及在其他dbms怎么计时
如果SQL不行,用存储过程解决也可以
/*构造测试数据*/
truncate table rk_mt;
truncate table rk_r;
truncate table rk_r2;
drop table rk_mt;
drop table rk_r;
drop table rk_r2;
set timi on
col dzm format '999999999999999';
create table rk_mt(uuid varchar(32),dzm number(14),huren number(5));
/* select sys_guid() from dual*/
insert into rk_mt select sys_guid(),mod(level,9999)+33260112300101, (mod(level,999)+1)*100+mod(level,99)+1 from dual connect by level<=1500000 ;
commit;
create table rk_r (uuid varchar(32),age number(3),high number(3),income number(4));
insert into rk_r select uuid, mod(dzm,160)+1,mod(huren,220)+30,mod(huren,9999)+1 from rk_mt;
commit;
create index mt_uuid on rk_mt(uuid);
create index r_uuid on rk_r(uuid);
create table rk_r2 (dzm number(14),huren number(5),age number(3),high number(3),income number(4));
--insert into rk_r2 select mod(level,9999)+33260112300101 ,(mod(level,999)+1)*100+mod(level,99)+1 , mod(level,160)+1,mod(level,220)+30,mod(level,9999)+1 from dual connect by level<=1500000;
insert into rk_r2 select rk_mt.dzm,rk_mt.huren,rk_r.age,rk_r.high,rk_r.income from rk_mt,rk_r where rk_mt.uuid=rk_r.uuid;
commit;
/*测试汇总速度*/
select count(*),avg(age) from rk_mt,rk_r where rk_mt.uuid=rk_r.uuid and dzm like '332601123002%'; /* uuid关联,条件查询*/
select count(*),avg(age) from rk_r2 where dzm like '332601123002%'; /* 直接条件查询*/
select count(*),avg(age),substr(dzm,10,3) from rk_mt,rk_r where rk_mt.uuid=rk_r.uuid group by substr(dzm,10,3); /* uuid关联,分组查询*/
select count(*),avg(age),substr(dzm,10,3) from rk_r2 group by substr(dzm,10,3); /* 直接分组查询*/
--50万数据量-(1)5.09-(2)0.84-(3)5.23-(4)1.44
--150万数据量-(1)15.59-(2)2.52-(3)17.15-(4)4.39 |
|