为测试效率的情况,我今天特意做了个实验
测试环境为以下[均无使用索引,机器为闲置的测试机]
SQL>; desc hzh_aa
Name Null? Type
------------------------------- -------- ----
ID NUMBER
TYPE NUMBER(38 )
SQL>; desc hzh_bb
Name Null? Type
------------------------------- -------- ----
ID NUMBER
LB VARCHAR2(2)
SQL>; desc hzh_cc
Name Null? Type
------------------------------- -------- ----
ID NUMBER
LB VARCHAR2(2)
SQL>;
运行以下代码往表中插内容:
declare
i number;
begin
for i in 1..100000 loop
insert into hzh_aa values(i,1);
insert into hzh_aa values(i,2);
end loop;
commit;
end;
/
declare
i number;
begin
for i in 1..100000 loop
insert into hzh_bb values(i,'OK');
insert into hzh_cc values(i,'NO');
end loop;
commit;
end;
/
set timing on --打开timing
select count(*) from (
select aa.id, decode(aa.type, 1, bb.lb, cc.lb)
from hzh_aa aa, hzh_bb bb, hzh_cc cc
where aa.id= bb.id (+)
and aa.id = cc.id (+));
连续三次执行的时间分别为:[7691] [7681] [7671]
select count(*) from (
select hzh_aa.id,lb
from hzh_aa,hzh_bb
where hzh_aa.id=hzh_bb.id and hzh_aa.type=1
union
select hzh_aa.id,lb
from hzh_aa,hzh_cc
where hzh_aa.id=hzh_cc.id and hzh_aa.type=2);
连续三次执行的时间分别为:[9193] [9133] [9153]
这是aa表只有20万条记录时的差距,在实际应用中的数据量是远远大于这个的。