查看执行计划发现一年的数据直接跑不动,如何优化可以提高跑的效率,现在我用的是按月限定A表的时间多次更新
有一个业务场景需要打标签,现在有两张表,一个持仓表A包含字段(市场代码mktcode,资产代码security(存在有空值的情况),资产代码security2,名称name),一个材料表B(市场代码mktcode,资产代码security,名称name,标识不同来源sk_issue1,sk_issue2),两张表都需要预处理,达到市场代码一致的情况。还有一张材料表C作为补充(市场代码mkt_code无需处理,资产代码security)。
with A1 as (select case when xxx end as mkt_code ,A.* from A),
with B1 as(select case when xxx end as mkt_code ,B.* from B)
现在的逻辑是将A表作为主要去关联材料表B,首先考虑通过A.security2||A.mkt_code =B.security||B.mkt_code,若关联不上再用A.security||A.mkt_code =B.security||B.mkt_code,还是关联不上的用名称去关联取值A.name||A.mkt_code =B.name||B.mkt_code,最后再关联补充材料表C,关联条件nvl(A.security2,A.security)||A.mkt_code =C.security||C.mkt_code。
大概逻辑如下
with A1 as (select case when xxx else null end as mkt_code ,A.* from A),
with B1 as(select case when xxx else null end as mkt_code ,B.* from B)
select securitycode
,mkt_code
,sk_issue
from (
select securitycode
,mkt_code
,sk_issue
,row()number(partition by securitycode,mkt_code order by name desc nulls last,sk_1 desc) rn
from (
select distinct nvl(t.security2,t.security) securitycode
,t.name
,coalesce(t.mkt_code,t1.mkt_code,t2.mkt_code,t3.mkt_code,t4.mkt_code) mkt_code
,coalesce(t1.sk_issue1,t1.sk_issue2,t2.sk_issue1,t2.sk_issue2,t3.sk_issue1,t3.sk_issue2) sk_1
,coalesce(t1.sk_issue1,t2.sk_issue1,t4.sk_issue,t3.sk_issue1,nvl(t.security2,t.security)) sk_issue
from A1 t
left join B1 t1
on t.security2||t.mkt_code =t1.security||t1.mkt_code
left join B1 t2
on t.security||t.mkt_code =t2.security||t2.mkt_code
left join B1 t3
on t.name||t.mkt_code =t3.name||t3.mkt_code
left join C
ON nvl(t.security2,t.security)||t.mkt_code =C.security||C.mkt_code
where nvl(t.security2,t.security)| is not null
)
)
where rn=1