Re: theory of rewriting a query

From: Andrew Kerber <andrew.kerber_at_gmail.com>
Date: Tue, 12 May 2009 13:34:15 -0500
Message-ID: <ad3aa4c90905121134h21481b94ob993859eef8c972c_at_mail.gmail.com>

One of the first things I look at in a case like this is the frequency of the query. If it is run often, it may well pay to put the subselects into a single materialized view, and add an appropriate index.

On Tue, May 12, 2009 at 1:27 PM, <Joel.Patterson_at_crowley.com> wrote:

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

-- 
Andrew W. Kerber

'If at first you dont succeed, dont take up skydiving.'

--
http://www.freelists.org/webpage/oracle-l

Received on Tue May 12 2009 - 13:34:15 CDT