티스토리 뷰

반응형

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....이런 순서로

출처 - 하정민 님

반응형
반응형
공지사항
최근에 올라온 글
최근에 달린 댓글
Total
Today
Yesterday
«   2024/03   »
1 2
3 4 5 6 7 8 9
10 11 12 13 14 15 16
17 18 19 20 21 22 23
24 25 26 27 28 29 30
31
글 보관함