Re: theory of rewriting a query
- From: Adam Musch <ahmusch@xxxxxxxxx>
- To: Joel.Patterson@xxxxxxxxxxx, Oracle-L@xxxxxxxxxxxxx
- Date: Tue, 12 May 2009 14:28:55 -0500
One use of temp tablespace -- an autotrace run through SQL plus would show
the number of disk sorts -- is a function of doing a GROUP BY in the query.
Either you need a larger sort area size (perhaps setting
WORKAREA_SIZE_POLICY = MANUAL and then setting SORT_AREA_SIZE and
SORT_AREA_RETAINED_SIZE as appropriate may cause fewer sort runs; again,
assuming that's a problem.
Another is the use -- again, without seeing the plan, I'm speculating -- is
that Oracle is doing a number of sort-merge joins due to the range-join
correlated subquery conditions in the where clause. 10gR2 is much more
aggressive about unnesting those types of subqueries than 9i was. You may
wish to see how execution plans and performance change when adding the
NO_UNNEST hint to those subqueries if running against 10gR2, assuming the
appropriate indexes are in place to support those min/max queries to limit
the number of blocks to be accessed.
Adam Musch
ahmusch@xxxxxxxxxxxxxxxxxxx
On Tue, May 12, 2009 at 1:27 PM, <Joel.Patterson@xxxxxxxxxxx> 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
>
>
>
> --
> //www.freelists.org/webpage/oracle-l
>
>
>
Other related posts:
