This discussion is archived
6 Replies Latest reply: Aug 7, 2012 1:47 PM by matthew_morris RSS

Reducing query cost

807385 Newbie
Currently Being Moderated
HI All,

The below given query is costs more and due that the application also taking more time.

Kindly help me to tune this query so that the cost will be less...

Thanks in advance.

My query
--------------

SELECT inwrd.in_number
FROM t_inward_det inwrd, t_io_policy_det pol,t_io_payment_det pay,
t_io_jointborrower_det joint
WHERE inwrd.in_number = pol.in_number
AND inwrd.in_number = pay.in_number(+)
AND inwrd.in_number = joint.in_number(+)
     AND ( p_inward_no IS NULL OR inwrd.in_number = p_inward_no)
AND ( p_proposal_no IS NULL OR pol.pl_prop_num = p_proposal_no OR pay.pl_prop_num = p_proposal_no)
AND ( p_policy_no IS NULL OR pol.pl_pol_num = p_policy_no OR pay.pl_pol_num = p_policy_no)
AND ( p_member_form IS NULL OR pol.form_no = p_member_form OR joint.form_num = p_member_form)
AND ( p_account_no IS NULL OR pol.hl_loan_acc_num = p_account_no)
GROUP BY inwrd.in_number;
  • 1. Re: Reducing query cost
    Karthick_Arp Guru
    Currently Being Moderated
    Please read {message:id=9360003}
  • 2. Re: Reducing query cost
    KuljeetPalSingh Guru
    Currently Being Moderated
    Is latest statistics available on tables/indexes?
    check last_analyzed coulmn on user_tables/user_indexes and gather the stats if required.
  • 3. Re: Reducing query cost
    Etbin Guru
    Currently Being Moderated
    Maybe getting read of some ORs
    SELECT inwrd.in_number
      FROM t_inward_det inwrd,
           t_io_policy_det pol,
           t_io_payment_det pay,
           t_io_jointborrower_det joint
     WHERE inwrd.in_number = pol.in_number
       AND inwrd.in_number = pay.in_number(+)
       AND inwrd.in_number = joint.in_number(+)
       AND inwrd.in_number = nvl(p_inward_no,inwrd.in_number) 
       AND (pol.pl_prop_num = nvl(p_proposal_no,pol.pl_prop_num)
        OR  pay.pl_prop_num = nvl(p_proposal_no,pay.pl_prop_num)
           )
       AND (pol.pl_pol_num = nvl(p_policy_no,pol.pl_pol_num)
        OR  pay.pl_pol_num = nvl(p_policy_no,pay.pl_pol_num)
           )
       AND (pol.form_no = nvl(p_member_form,pol.form_no)
        OR  joint.form_num = nvl(p_member_form,joint.form_num)
           ) 
       AND pol.hl_loan_acc_num = nvl(p_account_no,pol.hl_loan_acc_num)
     GROUP BY inwrd.in_number
    Regards

    Etbin
  • 4. Re: Reducing query cost
    Marcus Rangel Journeyer
    Currently Being Moderated
    And some NVLs.
  • 5. Re: Reducing query cost
    GVR Journeyer
    Currently Being Moderated
    Look at the cost for below query and see how it works.
         SELECT inwrd.in_number
      FROM t_inward_det           inwrd,
           t_io_policy_det        pol,
           t_io_payment_det       pay,
           t_io_jointborrower_det joint
     WHERE inwrd.in_number = pol.in_number
       AND inwrd.in_number = pay.in_number(+)
       AND inwrd.in_number = joint.in_number(+)
       AND 'INCLUDE' = Case
             when inwrd.in_number = nvl(p_inward_no, inwrd.in_number) then
              'INCLUDE'
             Else
              'I dont Need This'
           end      
       AND 'INCLUDE' = Case
             when pol.pl_prop_num = nvl(p_proposal_no, pol.pl_prop_num) then
              'INCLUDE'
             when pay.pl_prop_num = p_proposal_no then
              'INCLUDE'
             Else
              'I dont Need This'
           end
       AND 'INCLUDE' = Case
             when pol.pl_pol_num = nvl(p_policy_no, pol.pl_pol_num) then
              'INCLUDE'
             when pay.pl_pol_num = p_policy_no then
              'INCLUDE'
             Else
              'I dont Need This'
           end
          
       AND 'INCLUDE' = Case
             when pol.form_no = nvl(p_member_form, pol.form_no) then
              'INCLUDE'
             when joint.form_num = p_member_form then
              'INCLUDE'
             Else
              'I dont Need This'
           end      
       AND 'INCLUDE' = Case
           
             when pol.hl_loan_acc_num = nvl(p_account_no, pol.hl_loan_acc_num) then
              'INCLUDE'
             Else
              'I dont Need This'
           end
     GROUP BY inwrd.in_number;
    Edited by: GVR on Aug 7, 2012 1:29 PM
  • 6. Re: Reducing query cost
    matthew_morris Expert
    Currently Being Moderated
    It looks like this is part of a PL/SQL procedure that depends on parameters that may or may not be present. You should be able to use dynamic SQL to make the query adapt to which parameters are used.
      v_sql := 'SELECT inwrd.in_number ' ||
               'FROM t_inward_det inwrd, ' || 
               '     t_io_policy_det pol, ' ||
               '     t_io_payment_det pay, ' ||
               '     t_io_jointborrower_det joint ' ||
               'WHERE inwrd.in_number = pol.in_number ' ||
               'AND   inwrd.in_number = pay.in_number(+)
               'AND   inwrd.in_number = joint.in_number(+) ';
    
      IF p_inward_no IS NOT NULL THEN
        v_sql := v_sql || 'AND   inwrd.in_number = ' || p_inward_no || ' ';
      END IF;
    
      IF p_proposal_no IS NOT NULL THEN
        v_sql := v_sql || 'AND   ( pol.pl_prop_num = ' || p_proposal_no || 
                          ' OR pay.pl_prop_num = ' || p_proposal_no || ') ';
      END IF;
    
      IF p_policy_no IS NOT NULL THEN
        v_sql := v_sql || 'AND   ( pol.pl_pol_num = ' || p_policy_no || 
                          ' OR pay.pl_pol_num = ' || p_policy_no || ') ';
      END IF;
    
      IF p_member_form IS NOT NULL THEN
        v_sql := v_sql || 'AND   ( pol.form_no = ' || p_member_form || 
                          ' OR joint.form_num = ' || p_member_form || ') ';
      END IF;
    
      IF p_account_no IS NOT NULL THEN
        v_sql := v_sql || 'AND   pol.hl_loan_acc_num = ' || p_account_no || ' ';
      END IF;
    
      v_sql := v_sql || 'GROUP BY inwrd.in_number';
    
      EXECUTE v_sql;

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points