3 Replies Latest reply: Dec 11, 2012 7:53 PM by onedbguru RSS

    Rewriting SQL statement

    user11985838
      Hi group,

      I am working to rewrite this query for better performance, I am still trying to know how to do it :)

      UPDATE Q_VENTAS_ORA@ARTUS IMP
      SET IEPS=(NVL((SELECT V.TAX_RATE
      FROM RA_CUSTOMER_TRX_LINES_ALL V,
      AR_VAT_TAX_ALL T
      WHERE T.VAT_TAX_ID = V.VAT_TAX_ID
      AND V.LINE_TYPE = 'TAX'
      AND T.TAX_CODE LIKE '%IEPS%'
      AND V.CUSTOMER_TRX_ID = IMP.CUSTOMER_TRX_ID
      AND V.LINK_TO_CUST_TRX_LINE_ID = IMP.CUSTOMER_TRX_LINE_ID),0)/100) * IMP.PRECIO,
      IVA=(NVL((SELECT V.TAX_RATE
      FROM RA_CUSTOMER_TRX_LINES_ALL V,
      AR_VAT_TAX_ALL T
      WHERE T.VAT_TAX_ID           = V.VAT_TAX_ID
      AND V.LINE_TYPE           = 'TAX'
      AND T.TAX_CODE LIKE '%IVA%'
      AND V.CUSTOMER_TRX_ID = IMP.CUSTOMER_TRX_ID
      AND V.LINK_TO_CUST_TRX_LINE_ID = IMP.CUSTOMER_TRX_LINE_ID),0)/100) * ((IMP.PRECIO*(NVL((SELECT V.TAX_RATE
      FROM RA_CUSTOMER_TRX_LINES_ALL V,
      AR_VAT_TAX_ALL T
      WHERE T.VAT_TAX_ID = V.VAT_TAX_ID
      AND V.LINE_TYPE = 'TAX'
      AND T.TAX_CODE LIKE '%IEPS%'
      AND V.CUSTOMER_TRX_ID = IMP.CUSTOMER_TRX_ID
      AND V.LINK_TO_CUST_TRX_LINE_ID = IMP.CUSTOMER_TRX_LINE_ID),0)/100))+ IMP.PRECIO);


      I know this query can be rewritten in other way, but I am still thinking how to.

      Any advice or help, will be really appreciated.

      Thanks in advance.

      Kind regards,

      Francisco
        • 1. Re: Rewriting SQL statement
          sb92075
          HOW To Make TUNING request
          SQL and PL/SQL FAQ
          • 2. Re: Rewriting SQL statement
            SomeoneElse
            Oh what the heck, I'll take a crack at it. Rewriting it, that is. I'm assuming your original statement is logically correct.

            I don't have your tables and data so I have no idea how this will perform but at least it doesn't update every row in your table. (your original update has no WHERE clause)
            merge into q_ventas_ora@artus imp
            using (select v.customer_trx_id
                         ,v.link_to_cust_trx_line_id
                         ,min(case when t.tax_code like '%IEPS%' then nvl(v.tax_rate,0)/100 else null end) ieps_rate
                         ,min(case when t.tax_code like '%IVA%'  then nvl(v.tax_rate,0)/100 else null end) iva_rate
                   from   ra_customer_trx_lines_all v
                         ,ar_vat_tax_all t
                   where  t.vat_tax_id = v.vat_tax_id
                   and    v.line_type = 'TAX'
                   and    (t.tax_code like '%IEPS%'
                           or
                           t.tax_code like '%IVA%'
                          )
                   group  by v.customer_trx_id
                            ,v.link_to_cust_trx_line_id
                  ) u
            on    (    u.customer_trx_id          = imp.customer_trx_id
                   and u.link_to_cust_trx_line_id = imp.customer_trx_line_id
                  )
            when  matched then update
                  set imp.ieps = u.ieps_rate * imp.precio
                     ,imp.iva  = u.iva_rate * ((imp.precio * u.ieps_rate) + imp.precio)
            ;
            Not tested.
            • 3. Re: Rewriting SQL statement
              onedbguru
              What version ???

              With 11gR2 ( > 11.2.0.1), you can use DBMS_PARALLEL_EXECUTE procedure. If you truely need to update every row in the table, this procedure can help by breaking the table into tiny pieces and operating on it in parallel.

              Search the docs for this procedure and an example of how to code the procedure.