使用mass join优化DB2查询
使用mass join优化DB2查询
提问于 2014-05-20 02:29:19
我有一个复杂的查询：
select rma.RELATION_MANAGER_ID,
rm.ORG_STRUCTURE_ID,
rm.RELATIONSHIP_MANAGER_NM,
count(distinct ppa.PARTY_ID) as count_party
from RELATIONSHIP_MANAGER rm --15808 row
join RELATIONSHIP_MANAGER_MARKET rmm --1560 row
on rm.RELATIONSHIP_MANAGER_ID = rmm.RELATIONSHIP_MANAGER_ID
and rmm.INCLUDE_IN_REPORT = 'Y'
join MARKET_SEGMENT rm_ms --4 row
on rmm.MARKET_SEGMENT_ID = rm_ms.MARKET_SEGMENT_ID
and rm_ms.MARKET_SEGMENT = '01'
join RELATIONSHIP_MANAGER_ALLOCATION rma --61349 row
on rm.RELATIONSHIP_MANAGER_ID = rma.RELATIONSHIP_MANAGER_ID
join CMD_PARTY_PORTFOLIO_ALLOCATION ppa --3114096 row
on ppa.PORTFOLIO_ID = rma.PORTFOLIO_ID
join person ps --3112575 row
on ps.IS_DELETED != 1 and ppa.party_id = ps.party_id
join PARTY p --3114146 row
on ppa.party_id=p.party_id
join MARKET_SEGMENT ms --4 row
on p.MARKET_SEGMENT_ID = ms.MARKET_SEGMENT_ID and ms.MARKET_SEGMENT = '01'
where rm.IS_CM = 1 and rm.IS_DELETED != 1
group by rm.RELATIONSHIP_MANAGER_NM, rma.RELATIONSHIP_MANAGER_ID, rm.ORG_STRUCTURE_ID表列有索引：
rm.RELATIONSHIP_MANAGER_ID,
rmm.RELATIONSHIP_MANAGER_ID,
rmm.MARKET_SEGMENT_ID,
rm_ms.MARKET_SEGMENT_ID,
rma.RELATIONSHIP_MANAGER_ID,
ppa.PORTFOLIO_ID,
rma.PORTFOLIO_ID,
ppa.party_id,
ps.party_id,
p.party_id,
p.MARKET_SEGMENT_ID,
ms.MARKET_SEGMENT_ID表PARTY，PERSON有~1-300万行，查询运行时间~20秒。我是评论员
join MARKET_SEGMENT ms
on p.MARKET_SEGMENT_ID = ms.MARKET_SEGMENT_ID --and ms.MARKET_SEGMENT = '01'查询的运行时间变为~3秒。请解释一下为什么会发生这种情况？解释计划不要帮我..如何优化查询？
编辑:平台为DB2 for z/OS V9.7，增加了表的大小
EDIT2: explain plan显示第一个总是连接小尺寸的表
回答 1
Stack Overflow用户
发布于 2014-05-21 14:55:27
只是为了笑一笑，看看这是否有什么不同：
WITH
MktSeg( MARKET_SEGMENT_ID ) AS
( SELECT MARKET_SEGMENT_ID
FROM MARKET_SEGMENT
WHERE MARKET_SEGMENT = '01' )
select rma.RELATION_MANAGER_ID,
rm.ORG_STRUCTURE_ID,
rm.RELATIONSHIP_MANAGER_NM,
count(distinct ppa.PARTY_ID) as count_party
from RELATIONSHIP_MANAGER rm --15808 row
join RELATIONSHIP_MANAGER_MARKET rmm --1560 row
on rm.RELATIONSHIP_MANAGER_ID = rmm.RELATIONSHIP_MANAGER_ID
and rmm.INCLUDE_IN_REPORT = 'Y'
join MktSeg rm_ms --4 row
on rmm.MARKET_SEGMENT_ID = rm_ms.MARKET_SEGMENT_ID
join RELATIONSHIP_MANAGER_ALLOCATION rma --61349 row
on rm.RELATIONSHIP_MANAGER_ID = rma.RELATIONSHIP_MANAGER_ID
join CMD_PARTY_PORTFOLIO_ALLOCATION ppa --3114096 row
on ppa.PORTFOLIO_ID = rma.PORTFOLIO_ID
join person ps --3112575 row
on ps.IS_DELETED != 1 and ppa.party_id = ps.party_id
join PARTY p --3114146 row
on ppa.party_id=p.party_id
join MktSeg ms --4 row
on p.MARKET_SEGMENT_ID = ms.MARKET_SEGMENT_ID
WHERE rm.IS_CM = 1 AND rm.IS_DELETED != 1
group by rm.RELATIONSHIP_MANAGER_NM, rma.RELATIONSHIP_MANAGER_ID,
rm.ORG_STRUCTURE_ID, rma.RELATION_MANAGER_ID另请注意，我已经向Group By子句添加了一项。
页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/23744738
复制相关文章
相似问题
腾讯云开发者
