Query is running really slow... tuning recommendations appreciated!
DBAsupport.com Forums - Powered by vBulletin
Results 1 to 3 of 3

Thread: Query is running really slow... tuning recommendations appreciated!

  1. #1
    Join Date
    Apr 2014
    Posts
    5

    Query is running really slow... tuning recommendations appreciated!

    Hello everyone,

    I am a new Oracle DBA by accident not by trade and would greatly appreciate suggestions for the below query that is running very slow (more than 30 minutes and counting). I used the SQL Optimizer from Toad but it was unable to find a faster alternative for me. I've heard about self joins not sure if this is an option for this statement? I don't know much of anything in SQL or PL/SQL would appreciate your recommendations. Thank you!

    select a.EMPLID
    from ps_job a
    where (a.effdt = (select max(b.effdt) from ps_job b where b.emplid = a.emplid)
    and a.effseq = (select max(c.effseq) from ps_job c where c.emplid = a.emplid and c.effdt = a.effdt)
    and a.empl_rcd = (select max(d.empl_rcd) from ps_job d where d.emplid = a.emplid and d.effdt = a.effdt and d.effseq = a.effseq ))

  2. #2
    Join Date
    Nov 2000
    Location
    Pittsburgh, PA
    Posts
    4,001
    Here are two options. You should run each of these with autotrace on
    as well as timing on. You should also make sure that you are getting
    the right information. In your query you only wanted emplid, but you
    can just as easily get the other fields.

    You also did not explain why you did the joins that you did and whether
    or not you need to do the joins that way. It could be that both queries
    give you the same information, in that case the first query should be faster.

    Code:
    SELECT a.EMPLID, b.max_effdt, b.max_effseq, b.max_empl_rcd
      FROM ps_job a 
     INNER JOIN ( SELECT emplid,                 max(effdt)    max_effdt, 
                         max(effseq) max_effseq, max(empl_rcd) max_empl_rcd
                    FROM ps_job ) b
        ON a.emplid = b.emplid 
       AND a.effdt  = b.effdt 
       AND a.effseq = b.effseq
     ORDER BY 1;
    Code:
    SELECT a.EMPLID,  b.max_effdt, c.max_effseq, d.max_empl_rcd
      FROM ps_job a 
     INNER JOIN ( SELECT emplid,                 max(effdt) max_effdt, 
                    FROM ps_job ) b
        ON a.emplid = b.emplid 
     INNER JOIN ( SELECT emplid,                 max(effseq) max_effseq,
                    FROM ps_job ) c
        ON a.emplid = b.emplid 
       AND a.effdt  = b.effdt 
     INNER JOIN ( SELECT emplid,                 max(empl_rcd) max_empl_rcd
                    FROM ps_job ) d
        ON a.emplid = b.emplid 
       AND a.effdt  = b.effdt 
       AND a.effseq = b.effseq
     ORDER BY 1;
    this space intentionally left blank

  3. #3
    Join Date
    Apr 2014
    Posts
    5
    Thank you!

Tags for this Thread

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