--用in
SELECT m.offer_ins_id,m.subscriber_ins_id
--select count(*)
FROM ins1.UM_OFFER_xxx M where m.offer_type = '14' and m.offer_ins_id in(
select m2.offer_ins_id
from ins1.UM_BUND_OFFER_REL_xxx M2
where m2.spec_code = 'PMOfferFamilyMainRoleSpec'
)
--用exists
SELECT m.offer_ins_id,m.subscriber_ins_id
--select count(*)
FROM ins1.UM_OFFER_xxx M where m.offer_type = '14' and exists (
select null
from ins1.UM_BUND_OFFER_REL_xxx M2
where m2.spec_code = 'PMOfferFamilyMainRoleSpec' and m.offer_ins_id= m2.offer_ins_id
)
select /*+ parallel(8) */count(*) from ins1.um_subscriber_xxx t
select /*+ parallel(8) */count(*) from ins1.UM_OFFER_xxx M
SELECT /*+ parallel(8) */count(*) FROM ins1.UM_BUND_OFFER_REL_xxx M2
--测试结论,明显加了并行查询会提升查询速度
--um_offer_xxx数据量很大,也就两千多万吧
--看了网上结论,说B表小则用in反之用exists