DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: Query tunining

  1. #1
    Join Date
    Nov 2003
    Posts
    89

    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.

  2. #2
    Join Date
    Nov 2003
    Posts
    89
    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

  3. #3
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    edit you post using the [ code ] and [ / code ] tages properly, on both the explain plan and query

  4. #4
    Join Date
    Nov 2003
    Posts
    89
    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.

  5. #5
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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

  6. #6
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  7. #7
    Join Date
    Nov 2003
    Posts
    89
    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.

  8. #8
    Join Date
    Aug 2002
    Location
    Colorado Springs
    Posts
    5,253
    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.
    David Aldridge,
    "The Oracle Sponge"

    Senior Manager, Business Intelligence Development
    XM Satellite Radio
    Washington, DC

    Oracle ACE

  9. #9
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135
    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
  •  


Click Here to Expand Forum to Full Width