[ORACLE]SELECT INSERT 속도개선, nologging

alter table "해당 테이블" nologging -> 넣을 테이블

alter session set db_file_multiblock_read_count = 128; -> 옵션

insert /*+append*/ into "해당테이블"

옵션 주면 일반 full에 16배 더 빨라져
천 만건이면..어지간한 데이터도 30분 안 쪽으로 끝남

INSERT /*+APPEND/ INTO APLUSORA.NEW_ADDRESS_20120823
(
      BUPJUNG_CODE, SIDO, SIGUNGU,
   DONG, LI, SAN,
   BUNJI_NO1, BUNJI_NO2, DORO_CODE,
   DORO_NAME, JIHA, GUNMUL_NO1,
   GUNMUL_NO2, GUNMUL_NAME, GUNMUL_NAME_DTL,
   GUNMUL_GWANRI_NO, DONG_SEQ, HANGJUNG_CODE,
   HANGJUNG_NAME, ZIP_CODE, ZIP_CODE_SEQ,
   DELIVER_NAME, UPDATE_CODE, UPDATE_DAY,
   DORO_NAME_BEFORE, SIGUNGU_GUNMUL_NAME, APARTMENT
)
SELECT /*+FULL(APLUSORA.NEW_ADDRESS_20120823@APLUS_TEST_LINK) PARALLEL(APLUSORA.NEW_ADDRESS_20120823@APLUS_TEST_LINK, 4)/
   BUPJUNG_CODE, SIDO, SIGUNGU,
   DONG, LI, SAN,
   BUNJI_NO1, BUNJI_NO2, DORO_CODE,
   DORO_NAME, JIHA, GUNMUL_NO1,
   GUNMUL_NO2, GUNMUL_NAME, GUNMUL_NAME_DTL,
   GUNMUL_GWANRI_NO, DONG_SEQ, HANGJUNG_CODE,
   HANGJUNG_NAME, ZIP_CODE, ZIP_CODE_SEQ,
   DELIVER_NAME, UPDATE_CODE, UPDATE_DAY,
   DORO_NAME_BEFORE, SIGUNGU_GUNMUL_NAME, APARTMENT
FROM APLUSORA.NEW_ADDRESS_20120823@APLUS_TEST_LINK;

이렇게 Full 태우고 parallel 옵션주면 더 빨라짐

맨 처음 alter -> alter session set -> insert /append/ select /full....이런 순서로

출처 - 하정민 님