达梦数据库优化实践10

在sql优化过程中，子查询是否需要展开是影响执行效率的关键因素之一，在相关子查询中出现group by等汇总 *** 作可能造成cbo优化器无法将子查询在逻辑上展开成关联，以下案例中，使用分析函数over partition by开窗，消除自关联的方式帮助优化器将子查询扁平化，达到优化效果，执行时间从3000秒下降到0.002秒。
原sql如下，其中加粗部分使用自关联汇总 *** 作，造成了在逻辑上查询不能被rewrite成扁平化的情况：
select
t.table1TelexID ,
t.table1CommonPartID ,
CASE when o.table2dReadTime is null then ‘0’ else ‘1’ end as isRead,
CASE IsDate(o.table2dReplyTime) WHEN 1 THEN ‘1’ ELSE ‘0’ END AS isSign
from
(
select
t.table1TelexID,
o.table1CommonPartID
from
(
select
table2iID ,
table1TelexID,
table2cIsTransfer
from
tbTelexReceive t
where
t.table2vcDeleteFlag=‘0’
and t.table2iId =‘1458424’ /参数/
)
t,
tbTelexOARelationShip o
where
t.table1TelexID = o.table1TelexID
and t.table2cIsTransfer =‘1’
and o.table2vcDeleteFlag=‘0’
)
t,
(
select
table1CommonPartID,
table2dReplyTime ,
table2dReadTime
from
tbApproval a
where
table2dSortTime =
(
SELECt
Min(a1.table2dSortTime)
FROM
tbApproval a1
WHERe
a1.table1CommonPartID = a.table1CommonPartID
and Isnull(table2vcSendEname, ‘’)<>’’
and table2iflag =0
)
)
o
where
t.table1CommonPartID = o.table1CommonPartID

改写成如下sql，其中粗体部分使用row_nubmer（）分析函数替代自关联，cbo优化器可以将子查询rewrite成扁平化的方式：
select
tt.table1TelexID ,
tt.table1CommonPartID ,
CASE when tt.table2dReadTime is null then ‘0’ else ‘1’ end as isRead,
CASE IsDate(tt.table2dReplyTime) WHEN 1 THEN ‘1’ ELSE ‘0’ END AS isSign
from
(
select
t.table1TelexID,
o.table1CommonPartID,
a.table2dReplyTime,
a.table2dReadTime,
row_number() over(partition by a.table1CommonPartID order by a.table2dSortTime) rnm
from
(
select
table2iID ,
table1TelexID,
table2cIsTransfer
from
tbTelexReceive t
where
t.table2vcDeleteFlag=‘0’
and t.table2iId =‘1458424’ /参数/
)
t,
tbTelexOARelationShip o，
tbApproval a
where
t.table1TelexID = o.table1TelexID
and t.table2cIsTransfer =‘1’
and o.table2vcDeleteFlag=‘0’
and o.table1CommonPartID = a.table1CommonPartID
and Isnull(a.table2vcSendEname, ‘’)<>’’
and a.table2iflag =0
) tt
where tt.rnm = 1;
社区地址：https://eco.dameng.com