Блог

Пожалуйста, помогите со следующим видом процедурного кода, который мне нужно настроить. Основная предпосылка заключается в том, что мне нужно выбрать данные и вставить в таблицу. Я мог бы напрямую выполнить вставку в select, но для каждой строки требуется вычисление столбца clob.

Ниже приведена таблица и код.

     create table test_clob_performance
(col1 number , col2 number , col3 number , col4 number , col5 number , col6 number , col7 number , col8 number 
, clob1 clob , clob2 clob , clob3 clob , clob4 clob) ;



-- This is just a test function actual function derives ddata from tables on the basis of the input parameters
create or replace function some_func_for_clob(val number)
return clob
is 
begin
return rpad('test clob' , val , '*');
exception when others then 
raise_application_error(-20001 , sqlerrm||dbms_utility.format_error_stack);
end;




create or replace 
PROCEDURE insert_clob_performance IS

   CURSOR c1  IS
   SELECT  rownum  as col1, rownum 1 col2, rownum 1 col3, rownum 4 col4, rownum 5 col5,rownum 6 col6, rownum 7 col7
   , rownum 8  col8 from dual connect by rownum < 10000 ;

      var_clob1            CLOB;
      var_clob2           CLOB;
      var_clob3          CLOB;
      var_clob4           CLOB;

 BEGIN


   FOR z IN c1   LOOP

     var_clob1 := some_func_for_clob(z.col1 );
     var_clob2 := some_func_for_clob(z.col1 );
     var_clob3 := some_func_for_clob(z.col1 );
     var_clob4 := some_func_for_clob(z.col1 );

  INSERT /*  APPEND */ INTO test_clob_performance (col1 , col2 , col3 , col4 , col5 ,col6 , col7 , col8,clob1 , clob2 , clob3 , clob4)
  values (z.col1 , z.col2 , z.col3 , z.col4 , z.col5 ,z.col6 , z.col7 , z.col8,var_clob1 , var_clob2 , var_clob3 , var_clob4);


    END LOOP;
  END ;

 /
  

Например, выполнение insert_clob_performance занимает 17 секунд для завершения в базе данных Oracle 12.2. Когда я пытаюсь улучшить это с помощью массового сбора, производительность ухудшается, и для завершения insert_clob_performance_opt требуется 23 секунды.
Даже если я изменю предложение limit только на 100, производительность будет такой же, как в исходном коде.

Пожалуйста, предложите, что можно сделать для повышения производительности здесь.

 create or replace 
PROCEDURE insert_clob_performance_opt IS

   CURSOR c1  IS
   SELECT  rownum  as col1, rownum 1 col2, rownum 1 col3, rownum 4 col4, rownum 5 col5,rownum 6 col6, rownum 7 col7
   , rownum 8  col8 , '', '' , '' , '' from dual connect by rownum < 10000 ;

      var_clob1            CLOB;
      var_clob2           CLOB;
      var_clob3          CLOB;
      var_clob4           CLOB;

      type clob_perf_rec is table of test_clob_performance%rowtype ;
      clob_perf_tab clob_perf_rec;

 BEGIN

open c1 ;

  LOOP

  FETCH c1 bulk collect into clob_perf_tab LIMIT 1000;

  exit when clob_perf_tab.count = 0 ;

  for i in clob_perf_tab.first .. clob_perf_tab.last loop
    clob_perf_tab(i).clob1 := some_func_for_clob(clob_perf_tab(i).col1 );
    clob_perf_tab(i).clob2 := some_func_for_clob(clob_perf_tab(i).col2 );
    clob_perf_tab(i).clob3 := some_func_for_clob(clob_perf_tab(i).col3 );
    clob_perf_tab(i).clob4 := some_func_for_clob(clob_perf_tab(i).col4 );
  end loop;

  forall i in clob_perf_tab.first .. clob_perf_tab.last
    INSERT /*  APPEND */ INTO test_clob_performance (col1 , col2 , col3 , col4 , col5 ,col6 , col7 , col8,clob1 , clob2 , clob3 , clob4)
    values (clob_perf_tab(i).col1 , clob_perf_tab(i).col2 , clob_perf_tab(i).col3 , clob_perf_tab(i).col4 , 
    clob_perf_tab(i).col5 ,clob_perf_tab(i).col6 , clob_perf_tab(i).col7 , clob_perf_tab(i).col8,
    clob_perf_tab(i).clob1 , clob_perf_tab(i).clob2 , clob_perf_tab(i).clob3 , clob_perf_tab(i).clob4);


    END LOOP;
  END ;
  

Попробуйте сделать это с помощью одной инструкции insert-from-select:

 INSERT INTO test_clob_performance (col1 , col2 , col3 , col4 , col5 ,col6 , col7 , col8,clob1 , clob2 , clob3 , clob4)
SELECT col1 , col2 , col3 , col4 , col5 ,col6 , col7 , col8, some_func_for_clob(col1), some_func_for_clob(col2), some_func_for_clob(col3), some_func_for_clob(col4) 
FROM
(SELECT  rownum  as col1, rownum 1 col2, rownum 1 col3, rownum 4 col4, rownum 5 col5,rownum 6 col6, rownum 7 col7, rownum 8 col8 from dual connect by rownum < 10000) ;