4 Replies Latest reply: Jan 18, 2013 3:42 AM by Billy~Verreynne RSS

    Assign dynamic statement in declare block or begin block

    587126
      Hi all,

      For below code, if I assign the select statement in the declare section for corresponding variable, will it improve the performance.

      For cust_veh_id we have another cursor in declare block, only partial code is provided.


      What I meant.
      declare
      v_addr_chg_stmt                 varchar2(2000) := 'select ''Y''
      FROM cust_addrs addrs
      WHERE customer_id = :v_customer_id
      AND updated_date >:p_end_date
      and rownum = 1';
      Actaul
      ---------
      declare
      p_end_date                      date;
      v_addr_chg_stmt                 varchar2(2000);
      v_mileage_stmt                 varchar2(2000);
      begin
      
      select updated_date into p_end_date
      from process_log_rfsh
      where tran_code ='CAP';
      
      v_addr_chg_stmt :=
      'select ''Y''
      FROM cust_addrs addrs
      WHERE customer_id = :v_customer_id
      AND updated_date >:p_end_date
      and rownum = 1'
      ;
      
      v_mileage_stmt :=
      'SELECT ''Y'' FROM cust_vehicles_audit
      WHERE cust_veh_id=:v_cust_veh_id
      AND field_name =''LAST_MILEAGE''
      AND added_date > :p_end_date
      and audit_date >= trunc(:p_end_date)
      and rownum=1'
      ;
             begin
                       execute immediate v_addr_chg_stmt
                          into v_addr_change_flag
                          using v_customer_id,  p_end_date;
                   exception
                when no_data_found then
                       v_addr_change_flag:='N';
                   end;
              begin
                     execute immediate v_mileage_stmt
                       into v_mileage_change_flag
                      using v_cust_veh_id,  p_end_date, p_end_date;
                 exception
               when no_data_found then
                     v_mileage_change_flag:='N';
                 end;
      end;
      Thanks
      Raghu

      Edited by: Raghu on 18 Jan, 2013 1:13 PM
        • 1. Re: Assign dynamic statement in declare block or begin block
          Billy~Verreynne
          Raghu wrote:

          For below code, if I assign the select statement in the declare section for corresponding variable, will it improve the performance.
          Dynamic SQL is typically slower and always more dangerous than static SQL.

          Slower, because of parsing (especially when literals are used instead of bind variables).

          Dangerous because of uncertainty of whether that code will work at runtime, and issues like SQL injection.

          Yes, using bind variables reduces both. But static SQL in PL/SQL still remains superior in many ways. PL/SQL integrates the SQL language with the PL language for very sound reasons.

          Using PL/SQL with dynamic SQL, means that you are no longer using this tight cross language integration. Nullifying the greatest fundamental strength of the PL/SQL language.

          Why?

          There ALWAYS need to be sound and robust reasons to justify dynamic SQL in PL/SQL. What are your reasons?
          • 2. Re: Assign dynamic statement in declare block or begin block
            587126
            This code is already running in production from few years.

            If we have to use dynamic sql, performance wise defining in declare block or begin will be better.

            Thanks
            Raghu
            • 3. Re: Assign dynamic statement in declare block or begin block
              Purvesh K
              Raghu wrote:
              This code is already running in production from few years.

              If we have to use dynamic sql, performance wise defining in declare block or begin will be better.

              Thanks
              Raghu
              IMO, It would not.

              Moreover, since you know all the Object names and Column names, use of Dynamic SQL is a bizzarre.

              Is there anything that stops you from testing both the approaches?
              • 4. Re: Assign dynamic statement in declare block or begin block
                Billy~Verreynne
                Raghu wrote:
                This code is already running in production from few years.
                Not a valid reason to justify using dynamic SQL.

                YOU NEED JUSTIFICATION FOR USING DYNAMIC CODE. In any language.

                Again: WHAT is your justification? If you cannot provide that, then why are you using dynamic code??

                 
                If we have to use dynamic sql, performance wise defining in declare block or begin will be better.
                No difference - as neither method changes the actual SQL cursor executed. Neither makes the resulting cursor read data blocks faster.

                PL/SQL also does not care where variable assignment happens. The difference is so tiny and so small, it is irrelevant to performance:
                SQL> declare
                  2          t1      timestamp;
                  3  begin
                  4          --// test 1
                  5          t1 := systimestamp;
                  6          for i in 1..10000 loop
                  7                  declare
                  8                          num     number;
                  9                  begin
                 10                          num := 1;
                 11                  end;
                 12          end loop;
                 13          dbms_output.put_line( 'Test 1. '||to_char(systimestamp-t1) );
                 14  
                 15          --// test 2
                 16          t1 := systimestamp;
                 17          for i in 1..10000 loop
                 18                  declare
                 19                          num     number := 1;
                 20                  begin
                 21                          null;
                 22                  end;
                 23          end loop;
                 24          dbms_output.put_line( 'Test 2. '||to_char(systimestamp-t1) );
                 25  
                 26  end;
                 27  /
                Test 1. +000000000 00:00:00.000040000
                Test 2. +000000000 00:00:00.000024000
                
                PL/SQL procedure successfully completed.
                
                SQL>