Re: Doubt in rewriting SQL

You will have to at least give us some semantics around all the tables involved.
And prefereably some essential constraint information like PK's, UK's and FK's...
Of course we can 'deduce' a lot from the naming of the tables, columns and (guessed) join clauses.

But without the meaning of the database design: no can do. On Thu, Sep 25, 2008 at 6:03 PM, Dwaraknath Krishnamurthi < dwarak.k_at_gmail.com> wrote:

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

-- 
+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-+-
Toon Koppelaars
RuleGen BV
+31-615907269
toon_at_rulegen_dot_com
www_dot_rulegen_dot_com

Author: "Applied Mathematics for Database Professionals"

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