Re[1]:Problem SQL - cartesian join & LIOs !

From: Prem Khanna J <premj_at_rediffmail.com>
Date: 15 Oct 2004 06:56:45 -0000
Message-ID: <20041015065645.4561.qmail@webmail6.rediffmail.com>

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 = '‹‹—^QPTESTQB'

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.

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

Received on Fri Oct 15 2004 - 01:52:35 CDT