Re[1]:Problem SQL - cartesian join & LIOs !
- From: "Prem Khanna J" <premj@xxxxxxxxxxxxxx>
- To: "Mark Richard" <mrichard@xxxxxxxxxxxxxxxxx>
- Date: 15 Oct 2004 06:56:45 -0000
Thanx for the info' Mark . This is the SQL .
SELECT /*ordered index (prrv pay_run_result_values_pk)*/
pap.employee_number employee_number
,papr.payroll_name payroll_name
,pbt.balance_name b1
,pbd.dimension_name b2
,ppa.effective_date effective_date
,TO_NUMBER(prrv.result_value) * pbff.scale value
FROM
pay_all_payrolls_f papr
,pay_payroll_actions ppa
,pay_assignment_actions paa
,per_all_assignments_f paaf
,per_all_people_f pap
,pay_run_results prr
,pay_balance_types pbt
,pay_defined_balances pdb
,pay_balance_dimensions pbd
,pay_balance_feeds_f pbff
,pay_run_result_values prrv
WHERE
pap.person_id = paaf.person_id
AND TO_DATE('2005/02/22','YYYY/MM/DD')
BETWEEN pap.effective_start_date AND
pap.effective_end_date
AND TO_DATE('2005/02/22','YYYY/MM/DD')
BETWEEN paaf.effective_start_date AND
paaf.effective_end_date
AND TO_DATE('2005/02/22','YYYY/MM/DD')
BETWEEN papr.effective_start_date AND
papr.effective_end_date
AND TO_DATE('2005/02/22','YYYY/MM/DD')
BETWEEN pbff.effective_start_date AND
pbff.effective_end_date
AND paaf.assignment_type = 'E'
AND paaf.primary_flag = 'Y'
AND paaf.payroll_id = papr.payroll_id
AND ppa.action_status = 'C'
AND ppa.action_type = 'R'
AND ppa.payroll_action_id = paa.payroll_action_id
AND paaf.assignment_id = paa.assignment_id
AND paa.action_status = 'C'
AND paa.assignment_action_id = prr.assignment_action_id
AND prr.status = 'P'
AND pbd.balance_dimension_id = pdb.balance_dimension_id
AND pdb.balance_type_id = pbt.balance_type_id
AND pbt.balance_type_id = pbff.balance_type_id
AND pbff.input_value_id = prrv.input_value_id
AND prr.run_result_id = prrv.run_result_id
AND papr.payroll_id = ppa.payroll_id
AND ppa.effective_date = TO_DATE('2005/02/22','YYYY/MM/DD')
AND papr.payroll_name = '???^?QPTEST?QB'
Kind Regards,
Prem.
On Fri, 15 Oct 2004 Mark Richard wrote :
>I guess it's difficult to say confidently without seeing the SQL. >The plan
>shows the table it is cartesian joining on, so it should be quick to
>confirm / deny. Gut feeling would be that you are correct though. A
>single cartesian can be so dramatic that it's not worth looking >elsewhere
>until that issue in investigated and resolved if a problem is found >in the
>statement compared to desired results.
--
//www.freelists.org/webpage/oracle-l
Other related posts:
- » Re[1]:Problem SQL - cartesian join & LIOs !
