Results 1 to 4 of 4
  1. #1
    Join Date
    Apr 2008
    Posts
    5

    Question Unanswered: Performance Issues in group by

    Hi,

    Kindly help me in improving the query ..

    select led.GRANT_ID, led.FUND,fund.PROJ_STRT_DATE,fund.PROJ_END_DATE,

    p.PMT_METHOD_TYPE_IND, sum( led.budget)

    from grant_ledger led,grant_fund fund,payment_method p

    where led.grant_id = fund.grant_id
    and led.fund = fund.fund(+)
    and p.payment_type in ('A','B','C')
    and led.chart_of_accounts(+) = 'N'

    group by led.GRANT_ID, led.FUND,fund.PROJ_STRT_DATE,fund.PROJ_END_DATE,

    p.PMT_METHOD_TYPE_IND


    This seems to run for minutes..is der a way I could speed up but still having the group by functionality...

    Thanks..

  2. #2
    Join Date
    Mar 2007
    Posts
    623
    You select FROM three tables.
    But you only join LED and FUND. P is not joined to them.
    So the query returns for each (LED, FUND) combination all values in P complying given condition which multiply the result row count. It is called cross join or cartesian product.

    Do you really want that?
    How many rows are in all tables?

  3. #3
    Join Date
    Apr 2008
    Posts
    5
    Nope ...sorry I missed that one...I joined the third one again with the same primary key coming from the LED and pymt_code coming from Payment_Method...

    select led.GRANT_ID, led.FUND,fund.PROJ_STRT_DATE,fund.PROJ_END_DATE,

    p.PMT_METHOD_TYPE_IND, sum( led.budget),sum(led.ITD + led.CTD)

    from grant_ledger led,grant_fund fund,payment_method p

    where led.grant_id = fund.grant_id
    and led.grant_id = p.grnt_code
    and led.fund = fund.fund(+)
    and p.payment_type in ('A','B','C')
    and led.chart_of_accounts(+) = 'N'

    group by led.GRANT_ID, led.FUND,fund.PROJ_STRT_DATE,fund.PROJ_END_DATE,

    p.PMT_METHOD_TYPE_IND

    I think it is the aggregate function that is taking the most time ,bcoz im using more aggregates than wat i ve mentioned here...Any suggestions?

    Thanks

  4. #4
    Join Date
    Mar 2007
    Posts
    623
    > How many rows are in all tables?
    How much do the last two condition filter the output?

    It may be slow just because of the need to full scan large tables.
    Explain plan with cardinalities would really help in analyzing.

    When having performance problem, you shall read (and follow) http://www.billmagee.co.uk/oracle/sqltune/index.html (linked in the Guidance and resources for posters sticky).

    By the way, are you aware that the outer join operator is ignored as you do not use it on all conditions on FUND and LED tables.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •