I am trying to do to a bulk collect inside a loop which have dynamic SQL and execute multiple times based on input from loop then inserting into a table (and it is taking time approx. 4 mins to insert 193234 records). So as to try different different approach I think of using the bulk collect on select inside the loop and fill up a collection with each iteration of that loop lets say 1st iteration gives 10 rows then second gives 0 rows and 3rd returns 15 rows then the collection should hold 15 records at end of the loop. After exiting the loop I will use forall with collection I filled up inside loop to do an Insert at one go instead to doing insert for each iteration inside loop.

below is a sample code which is similar to application procedure I just use different tables to simplify question.

create table test_tab as select owner, table_name, column_name from all_tab_cols where 1=2;

create or replace procedure p_test
as

  l_sql varchar2(4000);
  type t_tab is table of test_tab%rowtype index by pls_integer;
  l_tab t_tab;
  l_tab1 t_tab;
  l_cnt number := 0;

begin

for i in (with tab as (select 'V_$SESSION' table_name from dual 
                       union all
                       select 'any_table'  from dual
                       union all
                       select 'V_$TRANSACTION' from dual
                       union all
                       select 'test_table' from dual
                       )
                    select table_name from tab )
loop
  
  l_sql := 'select owner, table_name, column_name from all_tab_cols where table_name = '''||i.table_name||'''';
  
 -- dbms_output.put_line(l_sql );
  execute immediate l_sql bulk collect into l_tab;
  
  dbms_output.put_line(l_sql ||' > '||l_tab.count);
  l_cnt := l_cnt  1;
  
  if l_tab.count<>0
  then
    l_tab1(l_cnt) := l_tab(l_cnt);
  end if;  
    
end loop;  
  dbms_output.put_line(l_tab1.count);
  forall i in indices of l_tab1
  insert into test_tab values (l_tab1(i).owner, l_tab1(i).table_name, l_tab1(i).column_name);

end;

It is inserting only 2 rows in test_tab table whereas as per my system it should insert 150 rows.

select owner, table_name, column_name from all_tab_cols where table_name = 'V_$SESSION' > 103
select owner, table_name, column_name from all_tab_cols where table_name = 'any_table' > 0
select owner, table_name, column_name from all_tab_cols where table_name = 'V_$TRANSACTION' > 47
select owner, table_name, column_name from all_tab_cols where table_name = 'test_table' > 0
2

Above is DBMS_OUTPUT from my system you may change the table names in loop if the example table names does not exists in your DB.

Oracle Version --

Oracle Database 19c Standard Edition 2 Release 19.0.0.0.0 - Production

CodePudding user response：

Firstly, do not use associative array collection for this, just use a nested-table collection type. You can concatenate nested-table collections using the MULTISET UNION ALL operator (avoiding needing to use loops).

CREATE TYPE test_type IS OBJECT(
  owner       VARCHAR2(30),
  table_name  VARCHAR2(30),
  column_name VARCHAR2(30)
);

CREATE TYPE test_tab_type IS TABLE OF test_type;

Then:

create procedure p_test
as
  l_sql CLOB := 'select test_type(owner, table_name, column_name) from all_tab_cols where table_name = :table_name';
  l_table_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
                                          'V_$SESSION',
                                          'ANY_TABLE',
                                          'V_$TRANSACTION',
                                          'TEST_TABLE'
                                        );
  l_tab  test_tab_type := test_tab_type();
  l_temp test_tab_type;
  l_cnt  number := 0;
BEGIN
  FOR i IN 1 .. l_table_names.COUNT LOOP
    EXECUTE IMMEDIATE l_sql BULK COLLECT INTO l_temp USING l_table_names(i);
  
    dbms_output.put_line(
      l_sql || ': ' || l_table_names(i) || ' > '||l_temp.count
    );
    l_cnt := l_cnt  1;
    l_tab := l_tab MULTISET UNION ALL l_temp;  
  END LOOP;

  dbms_output.put_line(l_tab.count);

  insert into test_tab
  SELECT *
  FROM   TABLE(l_tab);
end;
/

Secondly, don't do multiple queries if you can do it all in one query and use an IN statement; and if you do it all in a single statement then you do not need to worry about concatenating collections.

create or replace procedure p_test
as
  l_table_names SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST(
                                          'V_$SESSION',
                                          'ANY_TABLE',
                                          'V_$TRANSACTION',
                                          'TEST_TABLE'
                                        );
  l_tab  test_tab_type;
BEGIN
  select test_type(owner, table_name, column_name)
  bulk collect into l_tab
  from all_tab_cols
  where table_name IN (SELECT column_value FROM TABLE(l_table_names));

  dbms_output.put_line(l_tab.count);

  insert into test_tab
  SELECT *
  FROM   TABLE(l_tab);
end;
/

Thirdly, if you can do INSERT ... SELECT ... in a single statement the it will be much faster than using SELECT ... INTO ... and then a separate INSERT; and doing that means you do not need to use any collections.

create or replace procedure p_test
as
begin
  INSERT INTO test_tab (owner, table_name, column_name)
  select owner, table_name, column_name
  from   all_tab_cols
  where  table_name IN (
           'V_$SESSION',
           'ANY_TABLE',
           'V_$TRANSACTION',
           'TEST_TABLE'
         );
end;
/

fiddle

CodePudding user response：

For Oracle 19c I would suggest to use SQL_MACRO(table) to build dynamic SQL in place and use plain SQL. Below is an example that builds dynamic SQL based on all_tab_cols but it may be any other logic to build such SQL (with known column names and column order). Then you may use insert ... select ... without PL/SQL, because SQL macro is processed at the query parsing time.

Setup:

create table t1
as
select level as id, mod(level, 2) as val, mod(level, 2) as col
from dual
connect by level < 4;

create table t2
as
select level as id2, mod(level, 2) as val2, mod(level, 2) as col2
from dual
connect by level < 5;

create table t3
as
select level as id3, mod(level, 2) as val3, mod(level, 2) as col3
from dual
connect by level < 6;

Usage:

create function f_union_tables
return varchar2 sql_macro(table)
as
  l_sql varchar2(4000);
begin
  /*
    Below query emulates dynamic SQL with union
    of counts per columns COL* and VAL* per table,
    assuming you have only one such column in a table
  */
select
  listagg(
    replace(replace(
    /*Get a count per the second and the third column per table*/
    q'{
    select '$$table_name$$' as table_name, $$agg_cols$$, count(*) as cnt
    from $$table_name$$
    group by $$agg_cols$$
    }',
    '$$table_name$$', table_name),
    '$$agg_cols$$', listagg(column_name, ' ,') within group(order by column_name asc)
    ),
    chr(10) || ' union all ' || chr(10)
  ) within group (order by table_name) as result_sql

  into l_sql

from user_tab_cols
where regexp_like(table_name, '^T\d $')
  and (
    column_name like 'VAL%'
    or column_name like 'COL%'
  )
group by table_name;

  return l_sql;
end;/

select *
from f_union_tables()

fiddle