有一个表 aaaa
下面两个查询哪个快? 数据量300万条
自表关联查询:
Select *
From aaaa A, aaaa B
Where A.key1 = B.key1
and A.key2 = B.key2
and A.field1 = '1'
and A.field2 = '2'
and B.field1 = '3'
and B.field2 = '4'子查询:
select *
From
aaaa A,
(select *
from aaaa C
where C.field1 = '3'
and C.field2 = '4') B
where
A.key1 = B.key1
and A.key2 = B.key2
and A.field1 = '1'
and A.field2 = '2'
下面两个查询哪个快? 数据量300万条
自表关联查询:
Select *
From aaaa A, aaaa B
Where A.key1 = B.key1
and A.key2 = B.key2
and A.field1 = '1'
and A.field2 = '2'
and B.field1 = '3'
and B.field2 = '4'子查询:
select *
From
aaaa A,
(select *
from aaaa C
where C.field1 = '3'
and C.field2 = '4') B
where
A.key1 = B.key1
and A.key2 = B.key2
and A.field1 = '1'
and A.field2 = '2'
建议你的子查询能够变相的以WHERE方式查询
并且不建议使用IN 或者 ANY方法,也会影响查询速度
或者set timing on
然后执实际行后比较时间
where a.acct_month = '200602' and a.innet_month = '1'
and exists (select * from ods_user_info_fix b
where a.user_no=b.user_no
and b.acct_month = '200603'
and b.innet_month = '2')还是用exists 比较快些,我的ods_user_info_fix是个用户资料,每个月用400万以上的数据,按acct_month的分区表,user_no上有索引。
用exists 和 关联查询比较快。