oracle sql优化，两张临时表多次关联，如何优化

Question

查看执行计划发现一年的数据直接跑不动，如何优化可以提高跑的效率，现在我用的是按月限定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

Answer 1

WITH A1 AS ( SELECT CASE WHEN xxx THEN mktcode END AS mkt_code, A. FROM A ), 
     B1 AS ( SELECT CASE WHEN xxx THEN mktcode END AS mkt_code, B. FROM B ), 
     AB1 AS ( SELECT DISTINCT NVL(t.security2, t.security) AS securitycode, t.name, COALESCE(t.mkt_code, t1.mkt_code, t2.mkt_code, t3.mkt_code, t4.mkt_code) AS mkt_code, COALESCE(t1.sk_issue1, t1.sk_issue2, t2.sk_issue1, t2.sk_issue2, t3.sk_issue1, t3.sk_issue2) AS sk_1, COALESCE(t1.sk_issue1, t2.sk_issue1, t4.sk_issue, t3.sk_issue1, NVL(t.security2, t.security)) AS 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 ), 
     AB2 AS ( SELECT securitycode, mkt_code, sk_issue, ROW_NUMBER() OVER (PARTITION BY securitycode, mkt_code ORDER BY name DESC NULLS LAST, sk_1 DESC) AS rn FROM AB1 ) 
     SELECT securitycode, mkt_code, sk_issue FROM AB2 WHERE rn = 1;