Move large data between tables in oracle with bulk insert

Question

I want to move 1 million rows of data to another table. Im using query:

insert into Table1
select * from Table2;

in my PL/SQL function. But this way is too slow.

How can I do this with Bulk Insert method?

• Source and destination table has same structure.
• Tables have hash partition and 1 index.

Answer 1

Forget about bulk insert. Because the insert into select is the best bulk you can load. The fastest would be to disable the indexes (mark them unusable) and do this in a SINGLE insert:

insert /*+ append */ into TARGET
select COLS
  from SOURCE;

commit;

and rebuild the indexes using UNRECOVERABLE (and maybe even parallel).

PS: If the table is partitioned (Both source and target, you can even use parallel inserts)

FOLLOW UP:

Check the performance of the below select

SELECT    /*+ PARALLEL(A 4)
            USE_HASH(A) ORDERED */
      YOUR_COLS
FROM
      YOUR_TABLE A
WHERE
      ALL_CONDITIONS;

If faster then

INSERT   /*+ APPEND */
     INTO
      TARGET
    SELECT /*+ PARALLEL(A 4)
            USE_HASH(A) ORDERED */
          YOUR_COLS
    FROM
          YOUR_TABLE A
    WHERE
          ALL_CONDITIONS;