Results 1 to 9 of 9
  1. #1
    Join Date
    Jul 2009
    Posts
    44

    Unanswered: Join alternate to speed up a query

    My sample table looks like this

    ATTEMPT_________NAME_______SCORE
    1_______________Paul__________20
    1_______________Paul__________30
    1_______________Paul__________40
    2_______________Paul__________50
    1_______________Adam__________60
    2_______________Adam__________40
    2_______________Adam__________40
    2_______________Adam__________10

    What I'm aiming for, is to fetch the grand total of all scores per person, but this total should only be done for the last attempt. So my result should look like this

    ATTEMPT_________NAME_______TOT_SCORE
    2_______________Paul_________50
    2_______________Adam________90

    The SQL I could come up with was

    Code:
    select X.attempt, X.name, X.tot_score from
      (select attempt,name,sum(score) tot_score
       from TABLE group by attempt,name) X
      inner join
      (select name, MAX(attempt) from TABLE
      group by name
      ) Y
    on X.name=Y.name
    Although this SQL works fine, but takes forever to run because the sample TABLE I have used in my example as an abstraction is in reality a highly complex join in itself. Is there anyway I could achieve similar results by avoiding the self join here and hence make my query faster?

    Any help would be appreciated!

    thanks
    Last edited by rocker86; 08-28-12 at 23:48.

  2. #2
    Join Date
    Feb 2006
    Posts
    173
    Provided Answers: 1
    Try this:
    Code:
    select attempt, name, sum( tot_score ) as tot_score
     from  table
    where  attempt  =  '2'
    group by attempt, name


    Also can you create an index over the 'attempt' column.

  3. #3
    Join Date
    Jul 2009
    Posts
    44
    I wish I could do that
    The attempt sequence however is sequentially generated and I can't select for 'a particular' attempt. I'm looking for a generic solution which picks up the last attempt, whatever value that is.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I'm looking for a generic solution which picks up the last attempt, whatever value that is.
    So, you should supply enough sample data which would reflect your requirements.


    Anyway, how about this example?
    Code:
    SELECT attempt
         , name
         , SUM( score ) AS tot_score
     FROM  (SELECT s.*
                 , MAX( attempt )
                      OVER( PARTITION BY name ) AS max_attempt
             FROM  sample_table s
           )
     WHERE attempt = max_attempt
     GROUP BY
           name
         , attempt
    ;

    I have used in my example as an abstraction is in reality a highly complex join in itself.
    My another thought was "isn't there any simpler join to find last attempts for each name?".
    I want to see the highly complex join.

  5. #5
    Join Date
    Jul 2009
    Posts
    44
    So the join is complex simply because it joins a couple of badly indexed tables having rows in the order of hundreds of million. Hence I was hoping to avoid another join in the end and see if that makes it a little faster. But thanks for the suggested query tonkuma, I'll lpost the performance improvements if any as soon as I run it.

  6. #6
    Join Date
    Jul 2009
    Posts
    44
    p.s: Just wondering tonkuma, doesn't the above solution also do two full table scans?

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Your query might do full table scans twice for all joined tables(in subquery X and Y).
    My query might do full table scans once for all joined tables and once for an intermediate result of a subquery.

  8. #8
    Join Date
    Aug 2012
    Posts
    1
    can any one please provide me the default Oracle 11g data files, redo log files, and control files names.

  9. #9
    Join Date
    Oct 2002
    Location
    Cape Town, South Africa
    Posts
    253
    Another version that I suspect may have better performance assuming you have an index on (name, attempt):
    Code:
    select x.attempt, x.name, sum(x.score) tot_score 
      from TABLE x
     where x.attempt = (select MAX(t.attempt) 
                          from TABLE t
                         where t.name = x.name
                        group by t.name)
    group by attempt, name

Posting Permissions

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