Skip to Main Content

Fastest way to transfer data between tables

bonbonbaronFeb 4 2015 — edited Feb 5 2015

Using Oracle 11g, I want to copy a table from the primarily used schema, production database into my own personal schema, development database. I've been doing this daily like so:

insert into my_schema.table_name@development_DB

(select * from primary_schema.table_name@production_DB);

... and for a table of 6.7 million rows and 355 columns, this takes about 30 minutes.

I've been reading up on bulk collects and forall statements. I wrote code for a generic table-transfer procedure below, but it doesn't perform much better. For 6.7 million rows and 355 columns, it performs in 27:30. Nasty, right? So is there a way to tweak my bulk collect to perform at these "dramatically improved speeds" I keep hearing about??? I'm open to alternative methods as well.

create or replace procedure transfer_data(pis_table_name VARCHAR2) AS

  ls_src_table          VARCHAR2(100) := '<PRIMARY SCHEMA>.' || pis_table_name || '@<PRODUCTION DATABASE>';

  ls_tgt_table          VARCHAR2(100);

  LS_USERNAME           VARCHAR2(100);

  ls_anon_block         VARCHAR2(10000);

  ls_does_usr_have_tbl  VARCHAR2(1);

  ls_does_<PRIMARY>_have_tbl  VARCHAR2(1);

  le_tbl_not_exists     EXCEPTION;

  E_INVALID_USER        EXCEPTION;

  CURSOR c_does_user_have_tbl IS

    SELECT DISTINCT 'Y'

    FROM USER_TABLES

    WHERE upper(TABLE_NAME) = '' || upper(pis_table_name) || '';

  CURSOR c_does_<PRIMARY>_have_tbl IS

    SELECT DISTINCT 'Y'

    FROM ALL_TABLES

    WHERE upper(TABLE_NAME) = '' || upper(pis_table_name) || ''

    AND UPPER(OWNER) = '<PRIMARY>';

BEGIN

  SELECT USER INTO LS_USERNAME FROM DUAL;

  IF LS_USERNAME != '<PRIMARY>' THEN

    ls_tgt_table := LS_USERNAME || '.' || pis_table_name;

  ELSE

    RAISE E_INVALID_USER;

  END IF;

  OPEN c_does_user_have_tbl;

  FETCH c_does_user_have_tbl INTO ls_does_usr_have_tbl;

  CLOSE c_does_user_have_tbl;

  ls_does_usr_have_tbl := COALESCE(ls_does_usr_have_tbl,'N');

  OPEN c_does_<PRIMARY>_have_tbl;

  FETCH c_does_<PRIMARY>_have_tbl INTO ls_does_<PRIMARY>_have_tbl;

  CLOSE c_does_<PRIMARY>_have_tbl;

  ls_does_<PRIMARY>_have_tbl  := COALESCE(ls_does_<PRIMARY>_have_tbl  ,'N');

  ls_does_usr_have_tbl := COALESCE(ls_does_usr_have_tbl,'N');

  IF ls_does_usr_have_tbl = 'Y' THEN

    EXECUTE IMMEDIATE 'TRUNCATE TABLE ' || ls_tgt_table;

    COMMIT;

  ELSIF ls_does_<PRIMARY>_have_tbl = 'Y' AND ls_does_usr_have_tbl = 'N' THEN

    EXECUTE IMMEDIATE 'CREATE TABLE ' || ls_tgt_table || ' AS

                          SELECT *

                          FROM ' || ls_src_table || '

                          WHERE 1 = 0';

  ELSE

    RAISE le_tbl_not_exists;

  END IF;

  ls_anon_block := 'DECLARE

                      TYPE ltype_src_tbl IS TABLE OF ' || ls_src_table || '%ROWTYPE;

                      ltable_src  ltype_src_tbl := ltype_src_tbl();

                      CURSOR c_src_table IS

                        select *

                        from ' || ls_src_table ||';

                    BEGIN

                      OPEN c_src_table;

                      LOOP

                        FETCH c_src_table BULK COLLECT INTO ltable_src LIMIT 1000;

                        EXIT WHEN c_src_table%NOTFOUND;

                        FORALL I IN ltable_src.first .. ltable_src.last

                          INSERT INTO ' || ls_tgt_table || ' VALUES ltable_src(I);

                      END LOOP;

                      CLOSE c_src_table;

                      COMMIT;

                    END;';

  EXECUTE IMMEDIATE (ls_anon_block);

  DBMS_OUTPUT.PUT_LINE(sql%rowcount || ' rows inserted.');

  COMMIT;

EXCEPTION

WHEN le_tbl_not_exists THEN

  DBMS_OUTPUT.PUT_LINE('Table ' || ls_src_table || ' does not exist!');

WHEN E_INVALID_USER THEN

  DBMS_OUTPUT.PUT_LINE('This procedure is not designed for transferring data to main schema!'); 

END;

Comments
Post Details
Added on Feb 4 2015
6 comments
3,280 views