theory of rewriting a query
From: <Joel.Patterson_at_crowley.com>
Date: Tue, 12 May 2009 14:27:50 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA02C0D730_at_JAXMSG01.crowley.com>
GROUP BY a.company, a.union_cd, d.location, a.emplid, a.name, to_char(
Date: Tue, 12 May 2009 14:27:50 -0400
Message-ID: <0684DA55864E404F8AD2E2EBDFD557DA02C0D730_at_JAXMSG01.crowley.com>
Just off the top of anyones head going more for the theoretical side, can anyone suggest a better way to write the below peoplesoft query. I believe this uses a lot of temp tablespace, and something tells me the subqueries are an issue.
Maybe not, but would you in general unravel the subqueries and do it another way? Same type of querie I see a lot uses union all half a dozen times.
Just wondering
SELECT a.company, a.union_cd, d.location, a.emplid, a.name, to_char(
c.orig_hire_dt, 'YYYY-MM-DD'), f.name, a.erncd, sum(a.hours_crw) FROM ps_gl_transact_det a, ps_person c, ps_pers_srch_qry c1, ps_job d,
ps_emplmt_srch_qry d1, ps_job e, ps_emplmt_srch_qry e1, ps_person_name f WHERE c.emplid = c1.emplid AND c1.rowsecclass = 'HRSALLU' AND d.emplid = d1.emplid AND d.empl_rcd = d1.empl_rcd AND d1.rowsecclass = 'HRSALLU' AND e.emplid = e1.emplid AND e.empl_rcd = e1.empl_rcd AND e1.rowsecclass = 'HRSALLU' AND a.emplid = c.emplid AND a.emplid = d.emplid AND d.empl_rcd = a.empl_rcd AND a.cmc_trans_type = 'C' AND a.debit_credit = 'DB' AND a.union_cd IN ('ADT', 'AD3', 'ADO') AND a.erncd IN ('REG', 'OVR') AND a.gl_acctng_period BETWEEN :1 AND :2 AND d.effdt = (SELECT max(d_ed.effdt) FROM ps_job d_ed WHERE d.emplid = d_ed.emplid AND d.empl_rcd = d_ed.empl_rcd AND d_ed.effdt <= a.pay_end_dt) AND d.effseq = (SELECT max(d_es.effseq) FROM ps_job d_es WHERE d.emplid = d_es.emplid AND d.empl_rcd = d_es.empl_rcd AND d.effdt = d_es.effdt) AND d.reports_to = e.position_nbr AND e.effdt = (SELECT max(e_ed.effdt) FROM ps_job e_ed WHERE e.emplid = e_ed.emplid AND e.empl_rcd = e_ed.empl_rcd AND e_ed.effdt <= a.pay_end_dt) AND e.effseq = (SELECT max(e_es.effseq) FROM ps_job e_es WHERE e.emplid = e_es.emplid AND e.empl_rcd = e_es.empl_rcd AND e.effdt = e_es.effdt) AND e.emplid = f.emplid AND e.empl_status = 'A' AND f.name_type = 'PRI'
GROUP BY a.company, a.union_cd, d.location, a.emplid, a.name, to_char(
c.orig_hire_dt, 'YYYY-MM-DD'), f.name, a.erncd ORDER BY 2, 3, 7, 5
-- http://www.freelists.org/webpage/oracle-lReceived on Tue May 12 2009 - 13:27:50 CDT