Doubt in rewriting SQL

From: Dwaraknath Krishnamurthi <dwarak.k_at_gmail.com>
Date: Thu, 25 Sep 2008 21:33:01 +0530
Message-ID: <607321dc0809250903o6d3114cakfa1f6b86d499bdc2@mail.gmail.com>

Hi,I have a query which is performing bad and am trying to rewrite it. This is the existing Query

SELECT substr(ppf.employee_number,1,10) employee_number,

              substr(paf.assignment_number,1,12) assignment_number,
              substr(ppf.full_name,1,50) full_name,
              decode(ppf.employee_number,lag(ppf.employee_number) over
(order by paf.assignment_number), 0,1) emp_count,
              1 assignment_count,
              (SELECT hrl.meaning
         FROM hr_lookups hrl
              WHERE hrl.lookup_type = 'EMP_CAT'
     AND hrl.enabled_flag = 'Y'

    AND hrl.lookup_code = paf.employment_category) employment_category   FROM per_time_periods ptp

,per_assignments_f paf
,per_people_f ppf

             ,(SELECT paa.assignment_id
                 FROM   per_time_periods  ptp


              ,pay_payroll_actions ppa

                             ,pay_assignment_actions paa
              WHERE ptp.time_period_id       = 386582
                  AND   ppa.action_type         IN ('Q', 'R','V')
                  AND   ppa.action_status        = 'C'
                  AND   ppa.payroll_id           = 1119
                  AND   ppa.date_earned between ptp.start_date and
ptp.end_date
                  AND   ppa.payroll_action_id    = paa.payroll_action_id
                  AND   paa.run_type_id   IS  NULL) paid
  WHERE  paf.payroll_id         = 1119
      AND    ptp.time_period_id     = 386582
      AND    ptp.end_date     BETWEEN paf.effective_start_date AND
paf.effective_end_date
      AND    paf.assignment_id      = paid.assignment_id(+)
      AND    paid.assignment_id    IS NULL

      AND    paf.person_id          = ppf.person_id
      AND    paf.assignment_type = 'E'
      AND    ptp.end_date     BETWEEN ppf.effective_start_date AND

ppf.effective_end_date
order by 1,2

I am rewriting it as

SELECT substr(ppf.employee_number,1,10) employee_number,

              substr(paf.assignment_number,1,12) assignment_number,
              substr(ppf.full_name,1,50) full_name,
              decode(ppf.employee_number,lag(ppf.employee_number) over
(order by paf.assignment_number), 0,1) emp_count,
              1 assignment_count,
              (SELECT hrl.meaning
   FROM hr_lookups hrl
              WHERE hrl.lookup_type = 'EMP_CAT'
     AND hrl.enabled_flag = 'Y'

    AND hrl.lookup_code = paf.employment_category) employment_category   FROM per_time_periods ptp

,per_assignments_f paf
,per_people_f ppf

  WHERE  paf.payroll_id         = 1119
      AND    ptp.time_period_id     = 386582
      AND    ptp.end_date     BETWEEN paf.effective_start_date AND
paf.effective_end_date
      /*AND    paf.assignment_id      = paid.assignment_id(+)
      AND    paid.assignment_id    IS NULL*/
      AND  NOT exists (SELECT paa.assignment_id
                 FROM   per_time_periods  ptp


              ,pay_payroll_actions ppa

                             ,pay_assignment_actions paa
              WHERE ptp.time_period_id       = 386582
                  AND   ppa.action_type         IN ('Q', 'R','V')
                  AND   ppa.action_status        = 'C'
                  AND   ppa.payroll_id           = 1119
                  AND   ppa.date_earned between ptp.start_date and
ptp.end_date
                  AND   ppa.payroll_action_id    = paa.payroll_action_id
                  AND   paa.run_type_id   IS  NULL)
      AND    paf.person_id          = ppf.person_id
      AND    paf.assignment_type = 'E'
      AND    ptp.end_date     BETWEEN ppf.effective_start_date AND

ppf.effective_end_date
order by 1,2

I am moving the inline View 'Paid' to the where clause.

I have two doubts here.

1- Are both the queries equivalent? (I am not able to test this query with live data as of now so that i could confirm if both are equivalent using the results fetched)
2-Though I see a little reduction in the Cost when Explained, Still I am skeptic how it would perform in the Customer site

Please let me know if I am making any grave mistake :)

-- 
Thanks,
Dwarak.K

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

Received on Thu Sep 25 2008 - 11:03:01 CDT