-
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 ]
Last edited by srt; 02-10-2005 at 02:13 PM.
-
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
-
edit you post using the [ code ] and [ / code ] tages properly, on both the explain plan and query
-
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
Last edited by srt; 02-10-2005 at 02:13 PM.
-
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
-
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.
-
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.
-
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.
-
Do you have a trace file?
Tamil
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|