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 ))

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;

---

Thank you!