Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Is it correct way of adding two tables without having self-jo

RE: Is it correct way of adding two tables without having self-jo

From: Straub, Dan <Dan.Straub_at_McKesson.com>
Date: Fri, 26 Jul 2002 11:44:54 -0800
Message-ID: <F001.004A3C2A.20020726114454@fatcity.com> 





Have you done both an explain and sqltrace on the query to see how it is
being executed and where the time is being spent? I would start there.



Dan Straub 


McKesson Information Solutions 


541-681-8278 


44°03'N 123°05'W (or thereabouts) 


Confidentiality Notice: This e-mail message, including any attachments,
is for the sole use of the intended recipient(s) and may contain
confidential and privileged information.  Any unauthorized review, use,
disclosure or distribution is prohibited.  If you are not the intended
recipient, please contact the sender by reply e-mail and destroy all
copies of the original message.




-----Original Message----- 


<mailto:Sandeep_at_almoayyedintl.com.bh> ] 
Sent: Friday, July 26, 2002 6:13 AM 


To: Multiple recipients of list ORACLE-L 
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 <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_date 


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


AND rppa.date_earned BETWEEN asf.effective_start_date 
AND asf.effective_end_date 


AND ptp.time_period_id = rppa.time_period_id 
AND rppa.date_earned BETWEEN per.effective_start_date 
AND per.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. 



    
  
  1.   ppa.payroll_id = rppa.payroll_id 
  
  2.   rpaa.assignment_id = paa.assignment_id 
In above conditions, joining columns are same columns. Hence this is not
a Self Join scenario. 
  
  3.   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. 

  
  4.   paa.payroll_action_id = ppa.payroll_action_id 
  
  5.   ppa.effective_date >= rppa.effective_date 
Here, 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: 




    
  
  1.   Is it correct way of writing query? Imho, there is some mistake done
by development team. 
  
  2.   Not sure whether this view is giving desired result. 
  
  3.   As data is growing day by day, performance of this view is becoming
poor. Can we optimize this view? 






Any expert comments? 



Thanks. 


Sandeep. 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
<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). 


-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Straub, Dan
  INET: Dan.Straub_at_McKesson.com

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 - 14:44:54 CDT












Original text of this message













  HOME |
  ASK QUESTION |
  ADD INFO |
  SEARCH |
  E-MAIL US