RE: theory of rewriting a query
- From: "Kenneth Naim" <kennaim@xxxxxxxxx>
- To: <Joel.Patterson@xxxxxxxxxxx>, <oracle-l@xxxxxxxxxxxxx>
- Date: Tue, 12 May 2009 14:34:49 -0400
For this type of query I would user the analytical function row_number to
find the correct eff_dt per the other criteria and then join that query as
an inline view. This way it runs once instead of many times, you could
probably see a 2-3 order of magnitude performance increase if the tables are
large as they tend to be in peoplesoft.
Ken
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx]
On Behalf Of Joel.Patterson@xxxxxxxxxxx
Sent: Tuesday, May 12, 2009 2:28 PM
To: oracle-l@xxxxxxxxxxxxx
Subject: theory of rewriting a query
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
--
//www.freelists.org/webpage/oracle-l
--
//www.freelists.org/webpage/oracle-l
Other related posts:
