EXPLAIN: I'm running this SQL that takes >6 hours to run and I'm trying to see if this SQL code (please see below) could be made better. I realize that there are multiple joins from various tables, so it is a bit cumbersome. It should be noted that there are ~2 million rows. Any suggestions are much appreciated. THANKS!!!!
PLAN: Should I deconstruct this SQL into multiple tables and then union it all together? OR Is there a better approach? Sorry, I'm a bit lost in how to approach this problem b/c I was recently informed that I need to run this SQL everyday with only 1 thread.
select /*+ ordered full(t) full(s) full(l) full(b) parallel(72)*/
t.column1
,b.column1
,to_date(t.column2,'YYYYMMDD')
,to_date(b.column2,'YYYYMMDD')
,cr.column1
,cr.column2
,s.column1
,t.column3
,t.column4
,b.column4 as paid_clm_unique_key
,CASE WHEN t.column5 IN ('ASTR','HIHI','HITR','HOTR','PHTR') THEN 'YES'
WHEN t.column5 IN ('HIOI','HOHE','HOHO','HOHS','HOOO','HOOS') THEN 'NOPE'
ELSE 'MAYBE' END type_cd
,t.column6
,m.column6
,last_day(to_date(t.column7,'YYYYMMDD'))
,last_day(to_date(t.column8,'YYYYMMDD'))
,S.column8
,case when substr(S.column8,1,4) = '0999' then 'PREPAY' else 'OTHER' END REASON_CD
,S.column9
,' 'column10
,case when t.column9 = 'EM' then substr(l.column1,1,3) else substr(l.column1,3,5) end code1
,case when t.column9 = 'EM' then substr(l.column6,1,3) else substr(l.column6,3,5) end code2
,t.column10
,nvl(t.column11,'NVAL')
,t.column12
,t.column13
,t.column14
,t.column15
,nvl(t.column16,'NVAL')
,t.column7
,t.column17
,cr2.column17
,nvl(b.column17,0)
,nvl(b.column18,0)
,case when b.column1 is null then t.column17
when sum(b.column17) over (partition by t.column1) = 0 then (t.column17/count(b.column1) over (partition by t.column1))
else (b.column17/sum(b.column17) over (partition by t.column1)) * t.column17 end as d_amt_wdup
,t.column17/(case when sum(t.column17) over (partition by b.column1) = 0 then count(t.column1) over (partition by b.column1)
else sum(t.column17) over (partition by b.column1) end) * nvl(b.column17,0) as p_amt_wdup
,t.column17/(case when sum(t.column17) over (partition by b.column1) = 0 then count(t.column1) over (partition by b.column1)
else sum(t.column17) over (partition by b.column1) end) * nvl(b.column18,0) as p_n_amt_wdup
,case when row_number() over (partition by t.column10,nvl(t.column11,'NVAL'),t.column14
,t.column15,nvl(t.column16,'NVAL'),t.column17,b.column1
order by t.column7 desc,t.column1 desc) = 1 then 'N' else 'Y' end as duplicate
from TABLE1 t
join TABLE2 m on t.column20 = m.column20
join TABLE3 cr on m.column6 = cr.column20
join TABLE4 s on t.column1 = s.column20
join TABLE5 l on t.column1 = l.column20
left join TABLE1 b on b.column2 >= to_char(add_months(last_day(trunc(sysdate)),-13),'YYYYMMDD') --rolling 13 mos.
and b.column20 in ('EM','LV')
and t.column10 = b.column21
and nvl(t.column11,'NVAL') = nvl(b.column22,'NVAL')
and t.column15 = b.column23
and t.column3 = b.column24
and t.column14 = b.column25
and nvl(t.column16,'NVAL') = nvl(b.column26,'NVAL')
and t.column27 < nvl(b.column27,'99990101') ---paid at least 1 day after rejected
and t.column7 <= nvl(t.column28,'99990101') --makes sure paid not received before rejected
and t.column2 <= nvl(b.column2,'99990101') --paid mth after/equal to rejected mth
and nvl(b.column2,'99990101') >= nvl(b.column29,'00010101') --filter out future CCCC month
and b.column30 = 'C'
and b.column17 <> 0
left join TABLE2 m2 on b.column31 = m2.column31
left join TABLE3 cr2 on m2.column32= cr2.column32
where t.column2 >= to_char(add_months(last_day(trunc(sysdate)),-13),'YYYYMMDD') --rolling 13 mos.
and s.column40 >= to_char(add_months(last_day(trunc(sysdate)),-13),'YYYYMMDD') --rolling 13 mos.
and l.column40 >= to_char(add_months(last_day(trunc(sysdate)),-13),'YYYYMMDD') -- rolling 13 mos.
and t.column9 in ('EM','LV') and s.column50 in ('EM','LV') and l.column50 in ('EM','LV')
and t.column17 <> 0
and s.column60 not in ('Y','S','D')
and t.column60 = 'R'
group by t.column61
,b.column1
,t.column17
,t.column62
,b.column17
,b.column18
,t.column3
,to_date(t.column2,'YYYYMMDD')
,to_date(b.column2,'YYYYMMDD')
,t.column2
,cr.column1
,cr.column62
,s.column1
,t.column4
,CASE WHEN t.column5 IN ('ASTR','HIHI','HITR','HOTR','PHTR') THEN 'YES'
WHEN t.column5 IN ('HIOI','HOHE','HOHO','HOHS','HOOO','HOOS') THEN 'NOPE'
ELSE 'MAYBE' END
,t.column63
,m.column6
,t.column12
,t.column13
,cr2.column17
,last_day(to_date(t.column7,'YYYYMMDD'))
,last_day(to_date(t.column8,'YYYYMMDD'))
,S.column8
,case when substr(S.column8,1,4) = '0999' then 'PREPAY' else 'OTHER' END
,S.column9
,case when t.column9 = 'EM' then substr(l.column1,1,3) else substr(l.column1,3,5) end
,case when t.column9 = 'EM' then substr(l.column6,1,3) else substr(l.column6,3,5) end
,t.column10
,nvl(t.column11,'NVAL')
,t.column14
,t.column15
,nvl(t.column16,'NVAL')
,t.column7
,b.column4
