load large data into database
2 nologing
alter table table_name nologing
3 alter session enable parallel dml
4 insert /*+parallel(A1,8)*/ /*+ APPEND */ into A1 select /*+parallel(A2,10)*/ * from A2 ;
Or: alter table A1 parallel 8 //"8" 并行数,与CPU有关
alter table A2 parallel 10[@more@]
pl/sql实例:
alter session enable parallel dml;
DECLARE
PRAGMA AUTONOMOUS_TRANSACTION;
X NUMBER;
SUMNUM NUMBER;
INSERT_NUM NUMBER;
BEGIN
INSERT_NUM := 0;
X := 0;
SELECT COUNT(*) INTO SUMNUM FROM TEST;
WHILE X < SUMNUM
LOOP
INSERT /*+ append parallel(aa,2) */
INTO TEST1 AA
SELECT /*+parallel(bb,2)*/
*
FROM TEST BB
WHERE ROWID IN
(SELECT RID
FROM (SELECT ROWNUM RNO, ROWID RID
FROM (SELECT ROWID FROM TEST ORDER BY OBJECT_ID)
WHERE ROWNUM <= X + 1000)
WHERE RNO > X);
INSERT_NUM := INSERT_NUM + SQL%ROWCOUNT;
DBMS_OUTPUT.PUT_LINE('insert了:' || INSERT_NUM || ' 条记录!');
COMMIT;
X := X + 1000;
END LOOP;
DBMS_OUTPUT.PUT_LINE('insert了:' || INSERT_NUM || ' 条记录!');
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(SQLERRM);
DBMS_OUTPUT.PUT_LINE('insert了:' || INSERT_NUM || ' 条记录!');
END;
/
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/640706/viewspace-1047889/ ，如需转载，请注明出处，否则将追究法律责任。
