Hi all,
I need help in optimizing this query to use bulk collect and forall statements. I have created backup tables (BCK_xxxx) to copy all data from original tables (ORIG_xxx) but I am having problems converting this to bulk collect. Most examples I saw in BULK collect includes already defining the table name and structure using %rowtype. However, I have hundreds of tables to backup so I need my query specifically the table name to be dynamic. This my original query that inserts/deleted data one by one without bulk collect and takes a lot of time:
DECLARE
--select all table names from backup tables (ex: BCK_tablename)
CURSOR cur_temp_tbl IS
SELECT table_name
FROM all_tables
WHERE OWNER = 'BCKUP'
ORDER BY 1;
--select all table names from original tables (ex: ORIG_tablename)
CURSOR cur_original_tbl IS
SELECT table_name
FROM all_tables
WHERE OWNER = 'ORIG'
ORDER BY 1;
l_tbl_nm VARCHAR2(30 CHAR);
l_inserted_cnt number(5) :=0;
l_deleted_cnt number(5) :=0;
BEGIN
--first loop to delete all tables from backup
FOR a IN cur_temp_tbl LOOP
l_tbl_nm := a.table_name;
EXECUTE IMMEDIATE 'DELETE FROM '|| l_tbl_nm;
l_deleted_cnt := l_deleted_cnt +1;
END LOOP;
--second loop to insert data from original to backup
FOR b IN cur_original_tbl LOOP
l_tbl_nm := b.table_name;
CASE
WHEN INSTR(l_tbl_nm,'ORIG_') > 0 THEN
l_tbl_nm := REPLACE(l_tbl_nm,'ORIG_','BCK_');
ELSE
l_tbl_nm := 'BCK_' || l_tbl_nm;
END CASE;
EXECUTE IMMEDIATE 'INSERT INTO ' || l_tbl_nm || ' SELECT * FROM ' || b.table_name;
l_inserted_cnt := l_inserted_cnt +1;
END LOOP;
dbms_output.put_line('Deleted/truncated tables from backup :' ||l_deleted_cnt);
dbms_output.put_line('No of tables inserted with data from original to backup :' ||l_inserted_cnt);
EXCEPTION
WHEN OTHERS THEN
dbms_output.put_line(SQLERRM);
dbms_output.put_line(l_tbl_nm);
END;
--------------end of code-------------
I am thinking of including the code below to add on my second loop but I am having problems how to declare the 'cur_tbl' cursor and 'l_tbl_data' TABLE data type. I am unable to use rowtype since the tablename should be dynamic and will change in each iteration of my second loop that will list all table names from original table:
TYPE CurTblTyp IS REF CURSOR;
cur_tbl CurTblTyp;
TYPE l_tbl_t IS TABLE OF tablename.%ROWTYPE;
l_tbl_data l_tbl_t ;
OPEN cur_tbl FOR 'SELECT * FROM :s ' USING b.table_name;
FETCH cur_tbl BULK COLLECT INTO l_tbl_data LIMIT 5000;
EXIT WHEN cur_tbl%NOTFOUND;
CLOSE cur_tbl;
FORALL i IN 1 .. l_tbl_data .count
EXECUTE IMMEDIATE 'insert into '||l_tbl_nm||' values (:1)' USING l_tbl_data(i);
--------------end of code-------------
Hope you can help me and suggest how I can make this code much simpler. Thanks a lot.
BULK BINDING is designed to improve the performance of code that is doing single row operations. But your code is not doing that, it is already doing insert-select so you dont need to worry about converting to bulk bind.
My first question is - why not just use DataPump ? Seems a lot easier to me.
But if you want to keep the current scheme, some easy ways to improve the performance are:
1) change delete to truncate
EXECUTE IMMEDIATE 'DELETE FROM '|| l_tbl_nm;
becomes
EXECUTE IMMEDIATE 'truncate tabke '|| l_tbl_nm;
2) direct mode insert
EXECUTE IMMEDIATE 'INSERT INTO ' || l_tbl_nm || ' SELECT * FROM ' || b.table_name;
becomes
EXECUTE IMMEDIATE 'INSERT /*+ APPEND */ INTO ' || l_tbl_nm || ' SELECT * FROM ' || b.table_name;