Load data from one table to another(the faster way)
                                                 Last update (2013-10-03 11:37:56)
                                                                                                                   Date added (2008-02-29 19:11:47)

Summary
The keys for top speed are 2.
First the insert APPEND hint to the target table and PARALLEL FULL hint for scaning the source table.
And second the technique to make unusable the indexes on target table before inserting into it.

In our example will use a target table called OBJECTS, and will assume that has and one index.
CREATE TABLE OBJECTS 
AS SELECT * FROM ALL_OBJECTS 
WHERE 1=2;

CREATE INDEX OBJECTS_IDX ON OBJECTS(OBJECT_TYPE);
How to load fast
0. Truncate source table to clean old data (just for the example, you don't have to truncate every time)
TRUNCATE TABLE OBJECTS;
1. Make unusable the undelying indexes
ALTER INDEX OBJECTS_IDX UNUSABLE;
2. Enable parallel DML
ALTER SESSION ENABLE PARALLEL DML;
ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=32;
3. Load data from one table to another(the faster way). Do the insert, use parallel hint if you have multiple CPUs
INSERT /*+ APPEND PARALLEL(B) */ INTO OBJECTS B 
SELECT /*+ PARALLEL(A 16) */ * FROM ALL_OBJECTS A;

COMMIT;
4. Make indexes back valid with rebuild
ALTER INDEX OBJECTS_IDX REBUILD PARALLEL 16 NOLOGGING;
ALTER INDEX OBJECTS_IDX REBUILD NOPARALLEL;
Tip:If you make first unusable the index(step2) and truncate after(step1) then index will be VALID again!
Reviews
Filters
Search