Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2009
    Posts
    5

    Post Unanswered: SQL with group by is taking much time

    Hi Friends,

    Please advise on any hint can be added to reduce the amount of time taken for the below query or any possible tuning..

    select a.adj_feed_id, sum(abs(ADJ_LTD_AMT))from trial_balance a where business_date = '31-Mar-09' and a.adj_s_feed_id is not null and PROCESSING_LOCATION_CD ='EUR' group by a.adj_feed_id

    databaseracle 9i
    No. of rows in table 503041
    index is on business_date column

    ---Bali

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    Code:
    SELECT   a.adj_feed_id, 
             Sum(Abs(adj_ltd_amt)) 
    FROM     trial_balance a 
    WHERE    business_date = '31-Mar-09' 
             AND a.adj_s_feed_id IS NOT NULL 
             AND processing_location_cd = 'EUR' 
    GROUP BY a.adj_feed_id
    -- with Oracle characters between single quote marks are STRINGS!
    'this is a string 31-Mar-09; not a date'
    use TO_DATE with appropriate mask when DATE data type is needed

    >AND a.adj_s_feed_id IS NOT NULL
    may Results in a Full Table Scan

    Make sure all columns in WHERE clause have indexes.
    Make sure statistics are current on table & all indexes.


    post EXPLAIN_PLAN
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Mar 2009
    Posts
    5
    SELECT STATEMENT, GOAL = CHOOSE Cost=293595 Cardinality=82 Bytes=2132
    SORT GROUP BY Cost=293595 Cardinality=82 Bytes=2132
    PARTITION RANGE SINGLE
    TABLE ACCESS BY LOCAL INDEX ROWID Object owner=OPDTB Object name=TRIAL_BALANCE Cost=293595 Cardinality=5518 Bytes=143468
    INDEX RANGE SCAN Object owner=OPDTB Object name=OPD_IE11_TRIAL_BALANCE Cost=183540 Cardinality=551848

  4. #4
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    it is about as good as it gets.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  5. #5
    Join Date
    Dec 2003
    Posts
    1,074
    Surely an index on business_date would improve this query ...

  6. #6
    Join Date
    Mar 2002
    Location
    Reading, UK
    Posts
    1,137
    I think he mentions that the index is already on business_date. However performance might improve (depending on data distribution) by making an index on business_date,processing_location_cd and or adj_s_feed_id.

    Alan

  7. #7
    Join Date
    Dec 2003
    Posts
    1,074
    Just making sure ... asking for a single day seems like it would bring back a small set of records, and 500,000 records is already a small amount. I guess every record could have that same date theoretically.

    How slow is this query actually running? And what is your guesstimate on how long you'd like for it to take?

Posting Permissions

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