Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Mailing Lists -> Oracle-L -> Is it correct way of adding two tables without having self-join condition in Oracle HRMS
Hi Guys,
I need your comments on following view. This view has been written by Oracle Development guys. I raised iTAR for this view problem with Oracle HRMS group, and they suggested to raise it with RDBMS Group. When I raised iTAR with RDBMS Group, I get answer that Oracle guys are only for product support. And he has suggested to discuss this in Forums. I've put this question in Metalink forum as well as http://www.oracle.com/forums .
I am having tremendous problem with this view. Even if I select just 'select * from this_view;', it takes long time to give result.
May I have your comment please?
Thanks.
#############################################################################
Application: Oracle HRMS
Version: 11.5.3 & 11.5.5
View Name: PAY_ASSIGNMENT_ACTIONS_V2
View Query:
SELECT DISTINCT paa.ROWID row_id, paa.assignment_id, paa.assignment_action_id,
per.person_id, SUBSTR ( pay_gb_payroll_actions_pkg.get_salary ( asf.pay_basis_id, asf.assignment_id, rppa.date_earned), 1, 60 ) salary,
SUBSTR (INITCAP (hr_general.decode_pay_basis (asf.pay_basis_id)),1,30)pay_basis,
job.name job_title, per.full_name, per.title,
SUBSTR (per.first_name, 1, 1) || ' ' || SUBSTR (per.middle_names, 1, 1) initials, per.last_name, asf.assignment_number, asf.location_id,
asf.internal_address_line, per.national_identifier ni_number, per.expense_check_send_to_address,
rppa.date_earned date_earned, paa.payroll_action_id, rpaa.assignment_action_id run_assignment_action_id, rpaa.payroll_action_id run_payroll_action_id, rppa.time_period_id time_period_id, rppa.payroll_id payroll_id, NVL (rppa.pay_advice_date, ptp.pay_advice_date) pay_advice_date, ppg.segment1, ppg.segment2, ppg.segment3, ppg.segment4, ppg.segment5, ppg.segment6, ppg.segment7, ppg.segment8, ppg.segment9, ppg.segment10, ppg.segment11, ppg.segment12, ppg.segment13, ppg.segment14, ppg.segment15, ppg.segment16, ppg.segment17, ppg.segment18, ppg.segment19, ppg.segment20, ppg.segment21, ppg.segment22, ppg.segment23, ppg.segment24,ppg.segment25, ppg.segment26, ppg.segment27, ppg.segment28, ppg.segment29, ppg.segment30
FROM per_jobs job,
per_all_assignments_f asf, per_people_f per, pay_assignment_actions paa, /* prepayment assignment action */ pay_payroll_actions ppa, /* prepayment payroll action */ pay_assignment_actions rpaa, /* run assignment action */ pay_payroll_actions rppa, /* run payroll action */ pay_action_interlocks il, per_time_periods ptp, pay_people_groups ppg
WHERE paa.payroll_action_id = ppa.payroll_action_id
AND ppa.action_type IN ('U', 'P') AND ppa.action_status = 'C' AND ppa.payroll_id = rppa.payroll_id AND ppa.effective_date >= rppa.effective_dateAND rpaa.assignment_id = paa.assignment_id
AND ppg.people_group_id(+) = asf.people_group_id AND job.job_id(+) = asf.job_id AND per.person_id = asf.person_id AND paa.assignment_id = asf.assignment_idAND rppa.date_earned BETWEEN asf.effective_start_date AND asf.effective_end_date
AND rppa.payroll_action_id = rpaa.payroll_action_id AND rpaa.assignment_action_id = il.locked_action_id AND paa.assignment_action_id = il.locking_action_id AND il.ROWID = (SELECT SUBSTR ( MAX ( LPAD (aa.action_sequence, 15, 0) || loc.ROWID ), -18 ) /* Length of rowid */ latest_act FROM pay_assignment_actions aa, pay_action_interlocks loc WHERE loc.locked_action_id = aa.assignment_action_id AND loc.locking_action_id = paa.assignment_action_id);.
Observations:
1.Table pay_assignment_actions has been referenced 2 times in 'From' clause of view text. This is done generally in case of self join. But there is no self join condition written in 'Where' clause. Conditions for this table are as follows. a. ppa.payroll_id = rppa.payroll_id b. rpaa.assignment_id = paa.assignment_idIn above conditions, joining columns are same columns. Hence this is not a Self Join scenario.
2. Table pay_payroll_actions has also been referenced 2 times in 'From' clause of view text. Problem is exactly same as per above point. Condition for this table are as follows. a. paa.payroll_action_id = ppa.payroll_action_id b. ppa.effective_date >= rppa.effective_dateHere, difference is in condition 'b'. This seems to be inappropriate. This condition will result in Cartesian product. But because of 'distinct' clause in view text, it is not giving duplicate rows.
Questions:
Any expert comments?
Thanks.
Sandeep.
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Sandeep Kurliye
INET: Sandeep_at_almoayyedintl.com.bh
Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051 San Diego, California -- Public Internet access / Mailing Lists --------------------------------------------------------------------To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing). Received on Fri Jul 26 2002 - 08:13:29 CDT
![]() |
![]() |