Query tunining

[srt]

How to tune the below query to run faster. It takes hours.

[ CODE ]

create table resv2004.RT_cum_testload
UNRECOVERABLE
PCTFREE 0
PCTUSED 90
STORAGE (INITIAL 42M NEXT 100K PCTINCREASE 0)
as select nrtline ,to_number(to_char(to_date( greatest(198001,
to_char(to_date(lpad(to_char (nevaldate),4,'0'),'RRMM'),'RRRRMM')) , 'RRRRMM'),'YYYYQ') ) nEvalQtr ,1 + trunc((greatest(198001,to_char(to_date(lpad(to_char(nevaldate),4,'0'),'RRMM'),'RRRRMM'))
- 1 - 6)/100,0) nEvalShift ,to_number( to_char( to_date( greatest( 198001, decode(nvl(ncmadedate,0),0,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
99365,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
to_number(to_char(
to_date(to_char(to_date(lpad(to_char(ncmadedate),5,'0'),'RRDDD'),'RRRRDDD'),'RRRRDDD'),'RRRRMM'))
)), 'RRRRMM'),'YYYYQ')) nExposeQtr ,
to_number(to_char(to_date(greatest(198001,
to_char(to_date(lpad(to_char(nsufcredat),4,'0'),
'RRMM'),'RRRRMM')), 'RRRRMM'),'YYYYQ')) nRepQtr,
decode(nclaimno ,600000,2, 600001,2, 600002,2, 600003,2,600004,2,
600005,2, 600006,2, 0) +
decode( nclaimoffc, 89,1, 131,1, 877,1, 879,1, 0) newflag ,
5 nOrigin ,
cFiller1 ,
count(*) freq,
sum(Decode(ncorrflag, 1, 0, 2, 0, nreop$flag)) nclsdamt1 ,
sum(Decode(ncorrflag, 1, nreop$flag, 2, nreop$flag,0)) nopenamt1 ,
sum(ncumulpaid) ncumulpaid,
sum(Decode(ncorrflag, 1, 1, 2, 1, 0)) nopencount ,
sum(Decode(ncorrflag, 3, 1, 0)) ncwpcount ,
sum(Decode(ncorrflag, 4, 1, 0)) ncnpcount ,
sum(Decode(ncorrflag, 2, 1, 0)) nreopcount
FROM idb.homemain
WHERE
--nrtline in (30,31,32,33,34,35,36,37,38,39) and
to_number(to_char(to_date(lpad(to_char(nevaldate),4,'0'),'RRMM'),'RRRRMM'))
in (0307,0308,0309,0310,0311,0312,0401,0402,0403,0404,0405,0406)
and ncorrflag is not NULL
GROUP BY
nrtline ,
to_number(to_char(to_date( greatest(198001 ,
to_char(to_date(lpad(to_char(nevaldate),4,'0'),'RRMM'),'RRRRMM')) ,
'RRRRMM'),'YYYYQ') ),
1 + trunc((greatest(198001 ,
to_char(to_date(lpad(to_char(nevaldate),4,'0'),'RRMM'),'RRRRMM'))
- 1 - 6)/100,0),
to_number(
to_char(
to_date(
greatest(
198001,
decode(nvl(ncmadedate,0),0,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
99365,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
to_number(to_char(
to_date(to_char(to_date(lpad(to_char(ncmadedate),5,'0'),'RRDDD'),'RRRRDDD'),'RRRRDDD'),'RRRRMM'))
)), 'RRRRMM'),'YYYYQ')),
to_number(to_char(to_date(greatest(198001,
to_char(to_date(lpad(to_char(nsufcredat),4,'0'),'RRMM'),'RRRRMM') ) ,
'RRRRMM'),'YYYYQ') ),
decode(nclaimno ,600000,2, 600001,2, 600002,2, 600003,2,
600004,2, 600005,2, 600006,2, 0)
+ decode( nclaimoffc, 89,1, 131,1, 877,1, 879,1, 0),
cFiller1
UNION ALL
SELECT
nrtline ,
to_number(to_char(to_date( greatest(198001 ,
to_char(to_date(lpad(to_char(nfiller4),4,'0'),'RRMM'),'RRRRMM')) ,
'RRRRMM'),'YYYYQ') ) nEvalQtr ,
1 + trunc((greatest(198001 ,
to_char(to_date(lpad(to_char(nfiller4),4,'0'),'RRMM'),'RRRRMM'))
- 1 - 6)/100,0) nEvalShift ,to_number(
to_char(
to_date(
greatest(
198001,
decode(nvl(ncmadedate,0),0,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
99365,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
to_number(to_char(
to_date(to_char(to_date(lpad(to_char(ncmadedate),5,'0'),'RRDDD'),'RRRRDDD'),'RRRRDDD'),'RRRRMM'))
)), 'RRRRMM'),'YYYYQ')) nExposeQtr ,
to_number(to_char(to_date(greatest(198001, to_char(to_date(lpad(to_char
(nsufcredat),4,'0'),'RRMM'),'RRRRMM') ) , 'RRRRMM'),'YYYYQ') ) nRepQtr ,
decode(nclaimno ,600000,2, 600001,2, 600002,2, 600003,2,600004,2, 600005,2,
600006,2, 0) +
decode( nclaimoffc, 89,1, 131,1, 877,1, 879,1, 0) newflag ,
-5 nOrigin ,
cFiller1 ,
count(*) freq,
sum(Decode(ncorrflag, 1, 0, 2, 0, -nreop$flag)) nclsdamt1 ,
sum(Decode(ncorrflag, 1, -nreop$flag, 2, -nreop$flag,0)) nopenamt1 ,
sum(-ncumulpaid) ncumulpaid,
sum(Decode(ncorrflag, 1, -1, 2, -1, 0)) nopencount ,
sum(Decode(ncorrflag, 3, -1, 0)) ncwpcount ,
sum(Decode(ncorrflag, 4, -1, 0)) ncnpcount ,
sum(Decode(ncorrflag, 2, -1, 0)) nreopcount
FROM idb.homemain
WHERE
--nrtline in (30,31,32,33,34,35,36,37,38,39) and
to_number(to_char(to_date(lpad(to_char(nevaldate),4,'0'),'RRMM'),'RRRRMM'))
in (0307,0308,0309,0310,0311,0312,0401,0402,0403,0404,0405,0406)
and
to_number(to_char(to_date(lpad(to_char(nfiller4),4,'0'),'RRMM'),'RRRRMM'))
< 200406 +1 -- and
-- condition on ncorrflag is not needed, since we put nfiller4
-- on cumulative records only
GROUP BY
nrtline ,
to_number(to_char(to_date( greatest(198001 ,
to_char(to_date(lpad(to_char(nfiller4),4,'0'),'RRMM'),
'RRRRMM')) , 'RRRRMM'),'YYYYQ') ),
1 + trunc((greatest(198001 ,
to_char(to_date(lpad(to_char(nfiller4),4,'0'),'RRMM'),'RRRRMM'))
- 1 - 6)/100,0),
to_number(
to_char(
to_date(
greatest(
198001,
decode(nvl(ncmadedate,0),0,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
99365,
to_number(to_char(to_date(lpad(to_char(noccurdate),4,'0'),'RRMM'),'RRRRMM')),
to_number(to_char(
to_date(to_char(to_date(lpad(to_char(ncmadedate),5,'0'),'RRDDD'),'RRRRDDD'),'RRRRDDD'),'RRRRMM'))
)), 'RRRRMM'),'YYYYQ')),
to_number(to_char(to_date(greatest(198001,
to_char(to_date(lpad(to_char(nsufcredat),4,'0'),'RRMM'),'RRRRMM') ) ,
'RRRRMM'),'YYYYQ') ),
decode(nclaimno ,600000,2, 600001,2, 600002,2, 600003,2,600004,2, 600005,2,
600006,2, 0)
+ decode( nclaimoffc, 89,1, 131,1, 877,1, 879,1, 0),
cFiller1

[/CODE ]