RE: theory of rewriting a query
Date: Wed, 13 May 2009 15:28:13 -0500
Don't forget that analytic functions aren't free. Each function can cause it's own additional pass of the results set. I've seen plenty of examples where the query in question wasn't running as fast as expected. The query usually had multiple analytic functions, each requiring a separate pass of the results AND a dump to temp space.
David C. Herring | DBA, Acxiom Automotive
630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Kenneth Naim Sent: Tuesday, May 12, 2009 1:35 PM
To: Joel.Patterson_at_crowley.com; oracle-l_at_freelists.org Subject: RE: theory of rewriting a query
For this type of query I would user the analytical function row_number
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.
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Joel.Patterson_at_crowley.com Sent: Tuesday, May 12, 2009 2:28 PM
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.
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
The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.
If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.
If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.
http://www.freelists.org/webpage/oracle-l Received on Wed May 13 2009 - 15:28:13 CDT