Query tunining

Printable View

02-09-2005, 05:48 PM
srt

Query tunining

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 ]
02-09-2005, 05:56 PM
srt

I have index on nevaldate.

Explain plan

OPERATION OPTIONS ID PARENT_ID
---------------------------- ---------- ----------
CREATE TABLE STATEMENT ~ 0 ~
LOAD AS SELECT ~ 1 0
UNION-ALL ~ 2 1
SORT GROUP BY 3 2
TABLE ACCESS FULL 4 3
SORT GROUP BY 5 2
TABLE ACCESS FULL 6 5
02-09-2005, 06:19 PM
davey23uk

edit you post using the [ code ] and [ / code ] tages properly, on both the explain plan and query
02-10-2005, 12:12 PM
srt

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:

Explain plan OPERATION OPTIONS ID PARENT_ID ---------------------------- ---------- ---------- CREATE TABLE STATEMENT ~ 0 ~ LOAD AS SELECT ~ 1 0 UNION-ALL ~ 2 1 SORT GROUP BY 3 2 TABLE ACCESS FULL 4 3 SORT GROUP BY 5 2 TABLE ACCESS FULL 6 5
02-10-2005, 12:33 PM
tamilselvan

Do not type space after /

Avoid full table scan. you can see INDEX can help in the query. If you think, create index and play around it.

Tamil
02-10-2005, 12:33 PM
slimdave

why do you think that the operation shouldn't take as long as it does?

Also, edit your post and remove all the space inside the square brackets for the tags to work properly.
02-10-2005, 02:16 PM
srt

There is a index on nevaldate, but i guess index is not being used because it has function in the where clause. this query takes 6 hours of time.
Do you think it works with using any hints.
02-10-2005, 02:29 PM
slimdave

Well I think that the source of your problem is whatever is going on with the expression ...

Code:

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)

I would think that "to_char(...,'RRRRMM')" is giving you a six character strings beginning "20....", but you are converting it to a number and asking if the result is in a list of numbers that I don't think it can possibly match.

Storing dates in date formats would be a good start, for reasons that ought to be obvious based on the complexity of the expressions that you are using.
02-11-2005, 10:15 AM
tamilselvan

Do you have a trace file?

Tamil