3,490
社区成员
发帖
与我相关
我的任务
分享
with t1 as
(select level a1 from dual connect by level < 10000000),
t2 as
(select *
from (select level a2 from dual connect by level < 10000)
where a2 > 5000),
t3 as
(select level a3 from dual connect by level < 100000)
select *
from t1, t3
where t1.a1 = t3.a3
and not exists (select 2 from t2 where t2.a2 = t3.a3)
--18s--二次执行22s
with t1 as (select level a1 from dual connect by level < 10000000), t2 as
(select *
from (select level a2 from dual connect by level < 10000)
where a2 > 5000), t3 as
(select level a3 from dual connect by level < 100000)
select *
from t1
join t3
on t1.a1 = t3.a3
left join t2
on t2.a2 = t3.a3
where t2.a2 is null
--22s-- 二次执行22s
with t1 as
(select level a1 from dual connect by level < 10000000), t2 as
(select *
from (select level a2 from dual connect by level < 10000)
where a2 > 5000), t3 as
(select level a3 from dual connect by level < 100000)
select *
from t3
left join t2
on t3.a3 = t2.a2
join t1
on t3.a3 = t1.a1
where t2.a2 is null
--1s以内二次执行,也是1S以内。
--综上测试,先从t2中把t3数据剔出,然后和t1进行关联,这样效率更高。