Doubt in rewriting SQL
- From: "Dwaraknath Krishnamurthi" <dwarak.k@xxxxxxxxx>
- To: oracle-l@xxxxxxxxxxxxx
- Date: Thu, 25 Sep 2008 21:33:01 +0530
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
Other related posts:
