{"id":2892,"date":"2018-02-03T20:23:08","date_gmt":"2018-02-03T11:23:08","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=2892"},"modified":"2018-02-03T20:23:08","modified_gmt":"2018-02-03T11:23:08","slug":"sql%eb%a1%9c%eb%8d%94direct-path-load%ec%9d%98-%ea%b0%9c%eb%85%90-%eb%b0%8f-%ec%82%ac%ec%9a%a9-%eb%b0%a9%eb%b2%95","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=2892","title":{"rendered":"[SQL\ub85c\ub354]DIRECT PATH LOAD\uc758 \uac1c\ub150 \ubc0f \uc0ac\uc6a9 \ubc29\ubc95"},"content":{"rendered":"
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n
\n

\ub9e4\uc6b0 \ub9ce\uc740 \uc591\uc758 \ub370\uc774\ud0c0\ub97c \ube60\ub978 \uc2dc\uac04 \ub0b4\uc5d0\u00a0load\ud558\uace0\uc790\ud558\ub294 \uacbd\uc6b0\u00a0direct path load\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4. \uc5ec\uae30\uc5d0\uc11c \uc774\ub7ec\ud55c\u00a0direct path load\uc758 \uc790\uc138\ud55c \uac1c\ub150 \ubc0f \uc0ac\uc6a9\ubc29\ubc95,\uc0ac\uc6a9 \uc2dc \uace0\ub824\ud574\uc57c \ud560 \uc810 \ub4f1\uc744 \uc124\uba85\ud55c\ub2e4.<\/p>\n

 <\/p>\n

1. conventional path load<\/p>\n

\uc77c\ubc18\uc801\uc778\u00a0sql*loader\ub97c \uc774\uc6a9\ud55c \ubc29\ubc95\uc740 \uc874\uc7ac\ud558\ub294\u00a0table\uc5d0\u00a0datafile\ub0b4\uc758\u00a0data\ub97c\u00a0\u00a0SQL\uc758\u00a0INSERT command\ub97c \uc774\uc6a9\ud558\uc5ec\u00a0insert\uc2dc\ud0a8\ub2e4. \uc774\ub807\uac8c\u00a0SQL command\ub97c \uc774\uc6a9\ud558\uae30 \ub54c\ubb38\uc5d0 \uac01\uac01\uc758 \ub370\uc774\ud0c0\ub97c \uc704\ud55c\u00a0insert command\uac00 \uc0dd\uc131\ub418\uc5b4\u00a0parsing\ub418\ub294 \uacfc\uc815\uc774 \ud544\uc694\ud558\uba70, \uba3c\uc800\u00a0bind array buffer (data block buffer)\ub0b4\uc5d0\u00a0insert\ub418\ub294 \ub370\uc774\ud0c0\ub97c \uc785\ub825\uc2dc\ud0a8 \ud6c4 \uc774\uac83\uc744\u00a0disk\uc5d0\u00a0write\ud558\uac8c \ub41c\ub2e4.<\/p>\n

 <\/p>\n

conventional path load\ub97c \uc0ac\uc6a9\ud558\uc5ec\uc57c \ud558\ub294 \uacbd\uc6b0\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

— load\uc911\uc5d0\u00a0table\uc744\u00a0index\ub97c \uc774\uc6a9\ud558\uc5ec\u00a0access\ud558\uc5ec\uc57c \ud558\ub294 \uacbd\uc6b0<\/p>\n

direct load\uc911\uc5d0\ub294\u00a0index\uac00\u00a0‘direct load state’\uac00 \ub418\uc5b4 \uc0ac\uc6a9\uc774 \ubd88\uac00\ub2a5\ud558\ub2e4.<\/p>\n

— load\uc911\uc5d0\u00a0index\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uace0\u00a0table\uc744\u00a0update\ub098\u00a0insert\ub4f1\uc744 \uc218\ud589\ud574\uc57c\ud558\ub294 \uacbd\uc6b0\u00a0direct load\uc911\uc5d0\ub294\u00a0table\u00a0exclusive write(X) lock\uc744 \uac74\ub2e4.<\/p>\n

— SQL*NET\uc744 \ud1b5\ud574\u00a0load\ub97c \uc218\ud589\ud574\uc57c \ud558\ub294 \uacbd\uc6b0<\/p>\n

— clustered table\uc5d0\u00a0load\ud558\uc5ec\uc57c \ud558\ub294 \uacbd\uc6b0<\/p>\n

— index\uac00 \uac78\ub824 \uc788\ub294 \ud070\u00a0table\uc5d0 \uc801\uc740 \uc218\uc758 \ub370\uc774\ud0c0\ub97c\u00a0load\ud558\uace0\uc790 \ud560 \ub54c<\/p>\n

— referential\uc774\ub098\u00a0check integrity\uac00 \uc815\uc758\ub418\uc5b4 \uc788\ub294 \ud070\u00a0table\uc5d0<\/p>\n

load\ud558\uace0\uc790 \ud560 \ub54c<\/p>\n

— data field\uc5d0\u00a0SQL function\uc744 \uc0ac\uc6a9\ud558\uc5ec\u00a0load\ud558\uace0\uc790 \ud560 \ub54c<\/p>\n

 <\/p>\n

2. direct path load\uc758 \uc218\ud589 \uc6d0\ub9ac<\/p>\n

Direct Path Loads\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \ud2b9\uc9d5\ub4e4\ub85c \uc778\ud558\uc5ec \ub9e4\uc6b0 \ub9ce\uc740 \uc591\uc758 \ub370\uc774\ud0c0\ub97c \ube60\ub978\uc2dc\uac04\uc5d0\u00a0load\ud558\uace0\uc790 \ud560 \ub54c \uc774\uc6a9\ud558\ub294 \uac83\uc774 \ubc14\ub78c\uc9c1\ud558\ub2e4.<\/p>\n

(1) \u00a0SQL INSERT\ubb38\uc7a5\uc744\u00a0generate\ud558\uc5ec \uc218\ud589\ud558\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n

(2) \u00a0memory\ub0b4\uc758\u00a0bind array buffer\ub97c \uc774\uc6a9\ud558\uc9c0 \uc54a\uace0\u00a0database block\uc758\u00a0 format\uacfc \uac19\uc740\u00a0data\u00a0block\uc744\u00a0memory\uc5d0 \ub9cc\ub4e4\uc5b4 \ub370\uc774\ud0c0\ub97c \ub123\uc740 \ud6c4 \uadf8\ub300\ub85c\u00a0disk\uc5d0\u00a0write\ud55c\ub2e4. memory\ub0b4\uc758\u00a0block buffer\uc640\u00a0disk\uc758\u00a0block\uc740 \uadf8\u00a0format\uc774 \ub2e4\ub974\ub2e4.<\/p>\n

(3) \u00a0load \uc2dc\uc791 \uc2dc\uc5d0\u00a0table\uc5d0\u00a0lock\uc744 \uac78\uace0\u00a0load\uac00 \ub05d\ub098\uba74\u00a0release\uc2dc\ud0a8\ub2e4.<\/p>\n

(4) \u00a0table\uc758\u00a0HWM (High Water Mark)\uc717\ubd80\ubd84\uc758\u00a0block\uc5d0\u00a0data\ub97c\u00a0load\ud55c\ub2e4. HWM\ub294\u00a0table\uc5d0\u00a0data\uac00\u00a0insert\ub428\uc5d0 \ub530\ub77c \uacc4\uc18d \ub298\uc5b4\ub098\uace0\u00a0truncate \uc678\uc5d0\ub294 \uc904\uc5b4\ub4e4\uac8c \ud558\uc9c0 \ubabb\ud55c\ub2e4.\u00a0\u00a0\uadf8\ub7ec\ubbc0\ub85c, \ud56d\uc0c1 \uc644\uc804\ud788 \ube48 \uc0c8\ub85c\uc6b4\u00a0block\uc744 \ud560\ub2f9\ubc1b\uc544\u00a0data\ub97c \uc785\ub825\uc2dc\ud0a4\uac8c \ub41c\ub2e4.<\/p>\n

(5) \u00a0instance failure\uac00 \ubc1c\uc0dd\ud558\uc5ec\ub3c4\u00a0redo log file\uc744 \ud544\uc694\ub85c \ud558\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n

(6) \u00a0UNDO information\uc744 \ubc1c\uc0dd\uc2dc\ud0a4\uc9c0 \uc54a\ub294\ub2e4. \uc989\u00a0rollback segment\ub97c \uc0ac\uc6a9\ud558\uc9c0 \uc54a\ub294\ub2e4.<\/p>\n

(7) \u00a0OS\uc5d0\uc11c\u00a0asynchronous I\/O\uac00 \uac00\ub2a5\ud558\ub2e4\uba74, \ubcf5\uc218\uac1c\uc758\u00a0buffer\uc5d0 \uc758\ud574\uc11c \ub3d9\uc2dc\uc5d0\u00a0data\ub97c \uc77d\uc5b4\uc11c\u00a0buffer\uc5d0\u00a0write\ud558\uba74\uc11c\u00a0buffer\uc5d0\uc11c\u00a0disk\ub85c\u00a0write\ud560 \uc218 \uc788\ub2e4.<\/p>\n

(8) \u00a0parallel option\uc744 \uc774\uc6a9\ud558\uba74 \ub354\uc6b1 \uc131\ub2a5\uc744 \ud5a5\uc0c1\uc2dc\ud0ac \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

3. direct path load\uc758 \uc0ac\uc6a9\ubc29\ubc95 \ubc0f\u00a0options<\/p>\n

direct path load\ub97c \uc0ac\uc6a9\ud558\uae30 \uc704\ud55c\u00a0view\ub4e4\uc740 \ub2e4\uc74c\u00a0script\uc5d0 \ud3ec\ud568\uc5b4 \uc788\uc73c\uba70, \ubbf8\ub9ac\u00a0sys user\ub85c \uc218\ud589\ub418\uc5b4\uc57c \ud55c\ub2e4. \ub2e8 \uc774\u00a0script\ub294\u00a0catalog.sql\uc5d0 \ud3ec\ud568\ub418\uc5b4 \uc788\uc5b4,\u00a0\u00a0db \uad6c\uc131 \uc2dc\uc5d0 \uc774\ubbf8 \uc218\ud589\ub418\uc5b4\uc9c4\ub2e4.<\/p>\n

@$ORACLE_HOME\/rdbms\/admin\/catldr.sql<\/p>\n

 <\/p>\n

direct path load\ub97c \uc0ac\uc6a9\ud558\uae30 \uc704\ud574\uc11c\ub294 \uc77c\ubc18\uc801\uc778\u00a0sqlload \uba85\ub839\ubb38\uc5d0\u00a0DIRECT=TRUE\ub97c \ud3ec\ud568\uc2dc\ud0a4\uae30\ub9cc \ud558\uba74 \ub41c\ub2e4. \ub2e4\uc74c\uacfc \uac19\uc774 \uae30\uc220\ud558\uba74 \ub41c\ub2e4.<\/p>\n

sqlload username\/password control=loadtest.ctl direct=true<\/p>\n

 <\/p>\n

\uc774\u00a0direct path load\ub97c \uc0ac\uc6a9 \uc2dc\uc5d0 \uace0\ub824\ud560 \ub9cc\ud55c \ucd94\uac00\uc801\uc778\u00a0option \ubc0f\u00a0control file \ub0b4\uc5d0 \uae30\uc220 \uac00\ub2a5\ud55c\u00a0clause\ub4e4\uc744 \uc0b4\ud3b4\ubcf8\ub2e4.<\/p>\n

 <\/p>\n

(1) ROWS = n<\/p>\n

conventional path load\uc5d0\uc11c\u00a0rows\ub294\u00a0default\uac00\u00a064\uc774\uba70, rows\uc5d0 \uc9c0\uc815\ub41c \uac2f\uc218 \ub9cc\ud07c\uc758\u00a0row\uac00\u00a0load\ub418\uba74\u00a0commit\uc774 \ubc1c\uc0dd\ud55c\ub2e4. \uc774\uc640 \ube44\uc2b7\ud558\uac8c\u00a0direct load\u00a0\u00a0\u00a0path\uc5d0\uc11c\ub294\u00a0rows option\uc744 \uc774\uc6a9\ud558\uc5ec\u00a0data save\ub97c \uc774\ub8e8\uba70, data save\uac00 \ubc1c\uc0dd\ud558\uba74<\/p>\n

data\ub294 \uae30\uc874\u00a0table\uc5d0 \ud3ec\ud568\ub418\uc5b4 \uc785\ub825\ub41c\u00a0data\ub97c \uc783\uc9c0 \uc54a\uac8c \ub41c\ub2e4. \ub2e8 \uc774 \ub54c\u00a0direct path load\ub294 \ubaa8\ub4e0\u00a0data\uac00\u00a0load\ub41c \ub2e4\uc74c\uc5d0\uc57c\u00a0index\uac00 \uad6c\uc131\ub418\ubbc0\ub85c\u00a0data save\uac00 \ubc1c\uc0dd\ud558\uc5ec\ub3c4\u00a0index\ub294 \uc5ec\uc804\ud788\u00a0direct load state\ub85c \uc0ac\uc6a9\ud558\uc9c0 \ubabb\ud558\uac8c \ub41c\ub2e4.<\/p>\n

direct path load\uc5d0\uc11c \uc774\u00a0rows\uc758\u00a0default\uac12\uc740\u00a0unlimited\uc774\uba70, \uc9c0\uc815\ub41c \uac12\uc774\u00a0\u00a0\u00a0database block\uc744 \ucc44\uc6b0\uc9c0 \ubabb\ud558\uba74\u00a0block\uc744 \uc644\uc804\ud788 \ucc44\uc6b0\ub294 \uac12\uc73c\ub85c \uc62c\ub9bc\ud558\uc5ec,\u00a0\u00a0\u00a0partial block\uc774 \uc0dd\uc131\ub418\uc9c0 \uc54a\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n

 <\/p>\n

(2) PIECED clause<\/p>\n

control file\ub0b4\uc5d0\u00a0column_spec datatype_spec PIECED \uc21c\uc73c\ub85c \uae30\uc220\ud558\ub294 \uac83\uc73c\ub85c\uc11c\u00a0direct path load\uc5d0\ub9cc \uc720\ud6a8\ud558\ub2e4. LONG type\uacfc \uac19\uc774 \ud558\ub098\uc758\u00a0data\uac00\u00a0\u00a0maximum buffer size\ubcf4\ub2e4 \ud070 \uacbd\uc6b0 \ud558\ub098\uc758\u00a0data\ub97c \uc5ec\ub7ec\ubc88\uc5d0 \ub098\ub204\uc5b4\u00a0load\ud558\ub294 \uac83\uc774\ub2e4. \uc774\u00a0option\uc740\u00a0table\uc758 \ub9e8 \ub9c8\uc9c0\ub9c9\u00a0field \ud558\ub098\uc5d0\ub9cc \uc801\uc6a9\uac00\ub2a5\ud558\uba70, index column\uc778 \uacbd\uc6b0\uc5d0\ub294 \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4. \uadf8\ub9ac\uace0\u00a0load\ub3c4\uc911\u00a0data\uc5d0 \ubb38\uc81c\uac00 \uc788\ub294 \uacbd\uc6b0 \ud604\uc7ac\u00a0load\ub418\ub294\u00a0data\uc758 \uc798\ub9b0 \ubd80\ubd84\ub9cc\u00a0bad file\uc5d0 \uae30\ub85d\ub418\uac8c \ub41c\ub2e4. \uc65c\ub0d0\ud558\uba74 \uc774\uc804 \uc870\uac01\uc740 \uc774\ubbf8\u00a0datafile\uc5d0 \uae30\ub85d\ub418\uc5b4\u00a0buffer\uc5d0\ub294 \ub0a8\uc544\uc788\uc9c0 \uc54a\uae30 \ub54c\ubb38\uc774\ub2e4.<\/p>\n

 <\/p>\n

(3) READBUFFERS = n (default is 4)<\/p>\n

\ub9cc\uc57d \ub9e4\uc6b0 \ud070\u00a0data\uac00 \ub9c8\uc9c0\ub9c9\u00a0field\uac00 \uc544\ub2c8\uac70\ub098\u00a0index\uc758 \ud55c \ubd80\ubd84\uc778 \uacbd\uc6b0\u00a0PIECED option\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc5c6\ub2e4. \uc774\ub7ec\ud55c \uacbd\uc6b0\u00a0buffer size\ub97c \uc99d\uac00\uc2dc\ucf1c\uc57c \ud558\ub294\ub370 \uc774\uac83\uc740\u00a0readbuffers option\uc744 \uc774\uc6a9\ud558\uba74 \ub41c\ub2e4. default buffer\uac2f\uc218\ub294\u00a04\uac1c\uc774\uba70, \ub9cc\uc57d\u00a0data load\uc911\u00a0ORA-2374(No more slots for read buffer\u00a0 queue) message\uac00 \ub098\ud0c0\ub098\uba74, buffer\uac2f\uc218\uac00 \ubd80\uc871\ud55c \uac83\uc774\ubbc0\ub85c \ub298\ub824\uc8fc\ub3c4\ub85d \ud55c\ub2e4. \ub2e8 \uc77c\ubc18\uc801\uc73c\ub85c\ub294 \uc774\u00a0option\uc744 \uc774\uc6a9\ud558\uc5ec \uac12\uc744 \ub298\ub9b0\ub2e4\ud558\ub354\ub77c\ub3c4\u00a0system\u00a0 overhead\ub9cc \uc99d\uac00\ud558\uace0\u00a0performance\uc758 \ud5a5\uc0c1\uc740 \uae30\ub300\ud558\uae30 \uc5b4\ub835\ub2e4.<\/p>\n

 <\/p>\n

4. direct path load\uc5d0\uc11c\uc758\u00a0index \ucc98\ub9ac<\/p>\n

direct path load\uc5d0\uc11c \uc778\ub371\uc2a4\ub97c \uc0dd\uc131\ud558\ub294 \uc808\ucc28\ub294 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

 <\/p>\n

(1) data\uac00\u00a0table\uc5d0\u00a0load\ub41c\ub2e4.<\/p>\n

(2) load\ub41c\u00a0data\uc758\u00a0key \ubd80\ubd84\uc774\u00a0temporary segment\uc5d0\u00a0copy\ub418\uc5b4\u00a0sort\ub41c\ub2e4.<\/p>\n

(3) \uae30\uc874\uc5d0 \uc874\uc7ac\ud558\ub358\u00a0index\uc640\u00a0(2)\uc5d0 \uc758\ud574\uc11c \uc815\ub82c\ub41c\u00a0key\uac00\u00a0merge\ub41c\ub2e4.<\/p>\n

(4) (3)\uc5d0 \uc758\ud574\uc11c \uc0c8\ub85c\uc6b4\u00a0index\uac00 \ub9cc\ub4e4\uc5b4\uc9c4\ub2e4. \uae30\uc874\uc5d0 \uc874\uc7ac\ud558\ub358\u00a0index\uc640\u00a0temporary segment, \uadf8\ub9ac\uace0 \uc0c8\ub85c \ub9cc\ub4e4\uc5b4\uc9c0\ub294\u00a0index\uac00\u00a0 merge\uac00 \uc644\uc804\ud788 \ub05d\ub0a0 \ub54c\uae4c\uc9c0 \ubaa8\ub450 \uc874\uc7ac\ud55c\ub2e4.<\/p>\n

(5) old index\uc640\u00a0temporary segment\ub294 \uc9c0\uc6cc\uc9c4\ub2e4. \uc774\uc640 \uac19\uc740 \uc808\ucc28\uc5d0 \ubc18\ud574\u00a0conventional path load\ub294\u00a0data\uac00\u00a0insert\ub420 \ub54c\ub9c8\ub2e4 \ud55c row \uc529\u00a0index\uc5d0 \ucca8\uac00\ub41c\ub2e4. \uadf8\ub7ec\ubbc0\ub85c\u00a0temporary storage space\ub294 \ud544\uc694\ud558\uc9c0 \uc54a\uc9c0\ub9cc\u00a0\u00a0direct path load\uc5d0 \ube44\ud574\u00a0index \uc0dd\uc131 \uc2dc\uac04\ub3c4 \ub290\ub9ac\uace0, index tree\uc758\u00a0balancing\ub3c4 \ub5a8\uc5b4\uc9c0\uac8c \ub41c\ub2e4.\u00a0\u00a0index\uc0dd\uc131 \uc2dc \ud544\uc694\ud55c\u00a0temporary space\ub294 \ub2e4\uc74c\uacfc \uac19\uc740 \uacf5\uc2dd\uc5d0 \uc758\ud574 \uc608\uce21\ub418\uc5b4\uc9c8 \uc218 \uc788\ub2e4.<\/p>\n

 <\/p>\n

1.3 * key_storage<\/p>\n

key_storage = (number_of_rows) * (10 + sum_of_column_sizes + \u00a0number_of_columns)<\/p>\n

 <\/p>\n

\uc5ec\uae30\uc5d0\uc11c\u00a01.3\uc740 \ud3c9\uade0\uc801\uc73c\ub85c\u00a0sort \uc2dc\uc5d0 \ucd94\uac00\uc801\uc73c\ub85c \ud544\uc694\ud55c\u00a0space\ub97c \uc704\ud55c \uac12\uc774\uba70, \uc804\uccb4\u00a0data\uac00 \uc644\uc804\ud788 \uc21c\uc11c\uac00 \uac70\uafb8\ub85c \ub41c \uacbd\uc6b0\uc5d0\ub294\u00a02, \uc804\uccb4\uac00 \ubbf8\ub9ac \uc815\ub82c\ub41c \uacbd\uc6b0\ub77c\uba74\u00a0\u00a0\u00a01\uc744 \uc801\uc6a9\ud558\uba74 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

— SINGLEROW clause<\/p>\n

 <\/p>\n

\uc774\uc640 \uac19\uc774\u00a0direct path load\uc5d0\uc11c\u00a0index \uc0dd\uc131 \uc2dc\u00a0space\ub97c \ub9ce\uc774 \ucc28\uc9c0\ud558\ub294 \ubb38\uc81c\uc810 \ub54c\ubb38\uc5d0\u00a0resource\uac00 \ubd80\uc871\ud55c \uacbd\uc6b0\uc5d0\ub294\u00a0SINGLEROW option\uc744 \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4. \uc774\u00a0option\uc740\u00a0controlfile \ub0b4\uc5d0 \ub2e4\uc74c\uacfc \uac19\uc740 \ud615\ud0dc\ub85c \uae30\uc220\ud558\uba70, direct path\u00a0load\uc5d0\ub9cc \uc0ac\uc6a9 \uac00\ub2a5\ud558\ub2e4.<\/p>\n

 <\/p>\n

into tables table_name [sorted indexes…] singlerow<\/p>\n

 <\/p>\n

\uc774\u00a0option\uc744 \uc0ac\uc6a9\ud558\uba74 \uc804\uccb4\u00a0data\uac00\u00a0load\ub41c \ub4a4\uc5d0\u00a0index\uac00 \uad6c\uc131\ub418\ub294 \uac83\uc774 \uc544\ub2c8\ub77c\u00a0 data\uac00\u00a0load\ub428\uc5d0 \ub530\ub77c\u00a0data \uac01\uac01\uc774 \ubc14\ub85c\u00a0index\uc5d0 \ucd94\uac00\ub41c\ub2e4. \uc774\u00a0option\uc740 \uae30\uc874\uc5d0 \ubbf8\ub9ac\u00a0index\uac00 \uc874\uc7ac\ud558\ub294 \uacbd\uc6b0\u00a0index\ub97c \uc0dd\uc131\ud558\ub294 \ub3d9\uc548\u00a0 merge\ub97c \uc704\ud574\u00a0space\ub97c \ucd94\uac00\uc801\uc73c\ub85c \ud544\uc694\ub85c \ud558\ub294 \uac83\uc744 \ub9c9\uace0\uc790 \ud558\ub294 \uac83\uc774\ubbc0\ub85c\u00a0 INSERT \uc2dc\uc5d0\ub294 \uc0ac\uc6a9\ud558\uc9c0 \uc54a\uace0, APPEND\uc2dc\uc5d0\ub9cc \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud558\uace0 \uc788\ub2e4. \uc2e4\uc81c \uc0c8\ub85c\u00a0load\ud560\u00a0data \ubcf4\ub2e4 \uae30\uc874\u00a0table\uc774\u00a020\ubc30 \uc774\uc0c1 \ud074 \ub54c \uc0ac\uc6a9\ud558\ub3c4\ub85d \uad8c\ud558\uace0 \uc788\ub2e4.<\/p>\n

 <\/p>\n

direct path load\ub294\u00a0rollback information\uc744 \uae30\ub85d\ud558\uc9c0 \uc54a\uc9c0\ub9cc, \uc774\u00a0single row\u00a0 option\uc744 \uc0ac\uc6a9\ud558\uba74\u00a0insert\ub418\ub294\u00a0index\uc5d0 \ub300\ud574\u00a0undo \uc815\ubcf4\ub97c\u00a0rollback segment\uc5d0 \uae30\ub85d\ud558\uac8c \ub41c\ub2e4.<\/p>\n

 <\/p>\n

\uadf8\ub7ec\ub098, \uc911\uac04\uc5d0\u00a0instance failure\uac00 \ubc1c\uc0dd\ud558\uba74\u00a0data\ub294\u00a0data save\uae4c\uc9c0\ub294 \ubcf4\uc874 \ub418\uc9c0\ub9cc\u00a0index\ub294 \uc5ec\uc804\ud788\u00a0direct load state\ub85c \uc0ac\uc6a9\ud560 \uc218 \uc5c6\uac8c \ub41c\ub2e4.<\/p>\n

 <\/p>\n

— Direct Load State<\/p>\n

 <\/p>\n

\ub9cc\uc57d\u00a0direct path load\uac00 \uc131\uacf5\uc801\uc73c\ub85c \ub05d\ub098\uc9c0 \uc54a\uc73c\uba74\u00a0index\ub294\u00a0direct load\u00a0 \u00a0state\ub85c \ub41c\ub2e4. \uc774\u00a0index\ub97c \ud1b5\ud574 \uc870\ud68c\ud558\uace0\uc790 \ud558\uba74 \ub2e4\uc74c\uacfc \uac19\uc740 \uc624\ub958\uac00 \ubc1c\uc0dd\ud55c\ub2e4.<\/p>\n

 <\/p>\n

ORA-01502 : index ‘SCOTT.DEPT_PK’ is in direct load state.<\/p>\n

 <\/p>\n

index\uac00\u00a0direct load state\ub85c \ub418\ub294 \uc6d0\uc778\uc744 \uad6c\uccb4\uc801\uc73c\ub85c \uc0b4\ud3b4\ubcf4\uba74 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

(1) index\uac00 \uc0dd\uc131\ub418\ub294 \uacfc\uc815\uc5d0\uc11c\u00a0space\uac00 \ubd80\uc871\ud55c \uacbd\uc6b0<\/p>\n

(2) SORTED INDEXES clause\uac00 \uc0ac\uc6a9\ub418\uc5c8\uc73c\ub098, \uc2e4\uc81c\u00a0data\ub294 \uc815\ub82c\ub418\uc5b4 \uc788\uc9c0 \uc54a\uc740 \uacbd\uc6b0 \uc774\ub7ec\ud55c \uacbd\uc6b0\u00a0data\ub294 \ubaa8\ub450\u00a0load\uac00 \ub418\uace0, index\ub9cc\uc774\u00a0direct load state\ub85c \ub41c\ub2e4.<\/p>\n

(3) index \uc0dd\uc131 \ub3c4\uc911\u00a0instance failure\uac00 \ubc1c\uc0dd\ud55c \uacbd\uc6b0<\/p>\n

(4) unique index\uac00 \uc9c0\uc815\ub418\uc5b4 \uc788\ub294 \uceec\ub7fc\uc5d0 \uc911\ubcf5\ub41c\u00a0data\uac00\u00a0load\ub418\ub294 \uacbd\uc6b0 \ud2b9\uc815\u00a0index\uac00\u00a0direct load state\uc778\uc9c0\ub97c \ud655\uc778\ud558\ub294 \ubc29\ubc95\uc740 \ub2e4\uc74c\uacfc \uac19\ub2e4.<\/p>\n

 <\/p>\n

select index_name, status<\/p>\n

from user_indexes<\/p>\n

where table_name = TABLE_NAME’;<\/p>\n

 <\/p>\n

\ub9cc\uc57d\u00a0index\uac00\u00a0direct load state\ub85c \ub098\ud0c0\ub098\uba74 \uadf8\u00a0index\ub294\u00a0drop\ud558\uace0 \uc7ac\uc0dd\uc131 \ud558\uc5ec\uc57c\ub9cc \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4. \ub2e8, direct load \uc911\uc5d0\ub294 \ubaa8\ub4e0\u00a0index\uac00\u00a0direct\u00a0\u00a0load state\ub85c \ub418\uc5c8\ub2e4\uac00\u00a0load\uac00 \uc131\uacf5\uc801\uc73c\ub85c \ub05d\ub098\uba74 \uc790\ub3d9\uc73c\ub85c\u00a0valid\ub85c \ubcc0\uacbd\ub41c\ub2e4.<\/p>\n

 <\/p>\n

— Pre-sorting (SORTED INDEX)<\/p>\n

 <\/p>\n

direct load \uc2dc\u00a0index\uad6c\uc131\uc744 \uc704\ud574\uc11c \uc815\ub82c\ud558\ub294 \uc2dc\uac04\uc744 \uc904\uc774\uae30 \uc704\ud574 \ubbf8\ub9ac\u00a0index\u00a0\u00a0 column\uc5d0 \ub300\ud574\uc11c\u00a0data\ub97c \uc815\ub82c\ud558\uc5ec\u00a0load\uc2dc\ud0ac \uc218 \uc788\ub2e4. \uc774 \ub54c\u00a0control file \ub0b4\uc5d0\u00a0 SORTED INDEXES option\uc744 \ub2e4\uc74c\uacfc \uac19\uc774 \uc815\uc758\ud55c\ub2e4. \uc774\u00a0option\uc740\u00a0direct path load \uc2dc\uc5d0\ub9cc \uc720\ud6a8\ud558\uba70, \ubcf5\uc218 \uac1c\uc758\u00a0index\uc5d0 \ub300\ud574\uc11c \uc9c0\uc815\uac00\ub2a5\ud558\ub2e4.<\/p>\n

 <\/p>\n

into table table_name SORTED INDEXES (index_names_with_blank)<\/p>\n

 <\/p>\n

\ub9cc\uc57d, \uae30\uc874\uc758\u00a0index\uac00 \uc774\ubbf8 \uc874\uc7ac\ud55c\ub2e4\uba74, \uc0c8\ub85c\uc6b4\u00a0key\ub97c \uc77c\uc2dc\uc801\uc73c\ub85c \uc800\uc7a5\ud560 \ub9cc\ud07c \uc758\u00a0temporary storage\uac00 \ud544\uc694\ud558\uba70, \uae30\uc874\u00a0index\uac00 \uc5c6\ub294 \uacbd\uc6b0\uc600\ub2e4\uba74, \uc774\ub7ec\ud55c\u00a0 temporary space\ub3c4 \ud544\uc694\ud558\uc9c0 \uc54a\ub2e4.<\/p>\n

\uc774\uc640 \uac19\uc774\u00a0direct path load \uc2dc\uc5d0\u00a0index \uad6c\uc131 \uc2dc\uc5d0\ub294 \uae30\uc874 \ub370\uc774\ud0c0\uac00 \uc788\ub294\u00a0table\uc5d0\u00a0load\ud558\ub294 \uacbd\uc6b0\u00a0space\ub3c4 \ucd94\uac00\uc801\uc73c\ub85c \ub4e4\uace0, load\uac00 \uc644\uc804\ud788 \uc131\uacf5\uc801\uc73c\ub85c \ub05d\ub098\uc9c0 \uc54a\uc73c\uba74\u00a0\u00a0index\ub97c \uc7ac\uc0dd\uc131\ud558\uc5ec\uc57c \ud558\ubbc0\ub85c, \uc77c\ubc18\uc801\uc73c\ub85c\u00a0direct path load \uc804\uc5d0 \ubbf8\ub9ac\u00a0table\uc758\u00a0\u00a0index\ub97c \uc81c\uac70\ud55c \ud6c4\u00a0load\uac00 \ubaa8\ub450 \ub05d\ub09c \ud6c4 \uc7ac\uc0dd\uc131\ud558\ub3c4\ub85d \ud55c\ub2e4.<\/p>\n

 <\/p>\n

5. \u00a0Recovery<\/p>\n

direct load\ub294 \uae30\uc874\u00a0segment\uc911\uac04\uc5d0\u00a0data\ub97c\u00a0insert\ud558\ub294 \uac83\uc774 \uc544\ub2c8\ub77c \uc644\uc804\ud788 \uc0c8\ub85c\uc6b4\u00a0block\uc744 \ud560\ub2f9\ubc1b\uc544 \uc815\ud655\ud788\u00a0write\uac00 \ub05d\ub09c \ub2e4\uc74c \ud574\ub2f9\u00a0segment\uc5d0 \ud3ec\ud568\ub418\uae30 \ub54c\ubb38\uc5d0\u00a0instance failure\uc2dc\uc5d0\ub294\u00a0redo log\uc815\ubcf4\ub97c \ud544\uc694\ub85c \ud558\uc9c0 \uc54a\ub294\ub2e4. \uadf8\ub7ec\ub098\u00a0\u00a0default\ub85c\u00a0direct load\ub294\u00a0redo log\uc5d0 \uc785\ub825\ub418\ub294\u00a0data\ub97c \uae30\ub85d\ud558\ub294\ub370 \uc774\uac83\uc740\u00a0media\u00a0\u00a0recovery\ub97c \uc704\ud55c \uac83\uc774\ub2e4. \uadf8\ub7ec\ubbc0\ub85c\u00a0archive log mode\uac00 \uc544\ub2c8\uba74\u00a0direct load\uc5d0 \uc0dd\uc131\ub41c\u00a0redo log \uc815\ubcf4\ub294 \ubd88\ud544\uc694\ud558\uac8c \ub418\ubbc0\ub85c\u00a0NOARCHIVELOG mode\uc2dc\uc5d0\ub294 \ud56d\uc0c1\u00a0\u00a0control file\ub0b4\uc5d0\u00a0UNRECOVERABLE\uc774\ub77c\ub294\u00a0option\uc744 \uc0ac\uc6a9\ud558\uc5ec\u00a0redo log\uc5d0\u00a0redo entry\ub97c \uae30\ub85d\ud558\uc9c0 \uc54a\ub3c4\ub85d \ud55c\ub2e4.\u00a0\u00a0data\uac00\u00a0redo log \uc815\ubcf4 \uc5c6\uc774\u00a0instance failure\uc2dc\uc5d0\u00a0data save\uae4c\uc9c0\ub294 \ubcf4\ud638\ub418\ub294\ub370 \ubc18\ud574\u00a0index\ub294 \ubb34\uc870\uac74\u00a0direct load state\uac00 \ub418\uc5b4 \uc7ac\uc0dd\uc131\ud558\uc5ec\uc57c \ud55c\ub2e4. \uadf8\ub9ac\uace0\u00a0data save\uc774\ud6c4\uc758\u00a0load\ud558\uace0\uc790 \ud558\ub294\u00a0table\uc5d0 \ud560\ub2f9\ub418\uc5c8\ub358\u00a0extent\ub294\u00a0load\ub41c\u00a0data\uac00\u00a0\u00a0user\uc5d0\uac8c \ubcf4\uc5ec\uc9c0\uc9c0\ub294 \uc54a\uc9c0\ub9cc\u00a0extent\uac00\u00a0free space\ub85c\u00a0release\ub418\uc9c0\ub294 \uc54a\ub294\ub2e4.<\/p>\n

 <\/p>\n

6. Integrity Constraints & Triggers<\/p>\n

direct path load\uc911\u00a0not null, unique, primary key constraint\ub294\u00a0enable \uc0c1\ud0dc\ub85c \uc874\uc7ac\ud55c\ub2e4. not null\uc740\u00a0insert\uc2dc\uc5d0\u00a0check\ub418\uace0\u00a0unique\ub294\u00a0load\ud6c4\u00a0index\ub97c \uad6c\uc131\ud558\ub294 \uc2dc\uc810\uc5d0\u00a0check\ub41c\ub2e4. \uadf8\ub7ec\ub098\u00a0check constraint\uc640\u00a0referential constraint\ub294\u00a0load\uac00 \uc2dc\uc791\ub418\uba74\uc11c\u00a0disable\uc0c1\ud0dc\ub85c \ub41c\ub2e4. \uc804\uccb4 \ub370\uc774\ud0c0\uac00\u00a0load\ub418\uace0 \ub09c \ud6c4 \uc774\ub807\uac8c\u00a0disable\ub41c\u00a0\u00a0constraints\ub97c\u00a0enable\uc2dc\ud0a4\ub824\uba74\u00a0control file\ub0b4\uc5d0\u00a0REENABLE\uc774\ub77c\ub294\u00a0option\uc744 \uc9c0\uc815\ud558\uc5ec\uc57c \ud55c\ub2e4. \uc774\u00a0reenable option\uc740 \uac01\u00a0constraint\ub9c8\ub2e4 \uc9c0\uc815\ud560 \uc218\ub294 \uc5c6\uc73c\uba70\u00a0\u00a0control file\uc5d0 \ud55c\ubc88 \uc9c0\uc815\ud558\uba74 \uc804\uccb4\u00a0integrity\/check constraint\uc5d0 \uc601\ud5a5\uc744 \ubbf8\uce58\uac8c \ub41c\ub2e4. \ub9cc\uc57d\u00a0reenable\ub418\ub294 \uacfc\uc815\uc5d0\uc11c\u00a0constraint\ub97c \uc704\ubc30\ud558\ub294\u00a0data\uac00 \ubc1c\uacac\ub418\uba74 \ud574\ub2f9\u00a0constraint\ub294\u00a0enable\ub418\uc9c0 \ubabb\ud558\uace0\u00a0disabled status\ub85c \ub0a8\uac8c \ub418\uba70, \uc774\ub807\uac8c \uc704\ubc30\ub41c\u00a0data\ub97c \ud655\uc778\ud558\uae30 \uc704\ud574\uc11c\ub294\u00a0reenable clause\uc5d0\u00a0exceptions option\uc744 \ub2e4\uc74c\uacfc \uac19\uc774 \ucd94\uac00\ud558\uba74 \ub41c\ub2e4.<\/p>\n

 <\/p>\n

reenable [exceptions table_name]<\/p>\n

 <\/p>\n

\uc774 \ub54c\u00a0table_name\uc740\u00a0$ORACLE_HOME\/rdbms\/admin\/utlexcpt.sql\uc744 \ub2e4\ub978\u00a0\u00a0directory\ub85ccopy\ud558\uc5ec\u00a0table\uc774\ub984\uc744\u00a0exceptions\uac00 \uc544\ub2cc \ub2e4\ub978 \uc774\ub984\uc73c\ub85c \ub9cc\ub4e4\uc5b4 \uc218\ud589\uc2dc\ud0a4\uba74 \ub41c\ub2e4.\u00a0\u00a0\u00a0insert trigger\ub3c4\u00a0integrity\/check constraint\uc640 \uac19\uc774\u00a0direct load\uac00 \uc2dc\uc791\ud558\ub294 \uc2dc\uc810\uc5d0\u00a0disable\ub418\uba70, load\uac00 \ub05d\ub098\uba74 \uc790\ub3d9\uc73c\ub85c\u00a0enable\ub41c\ub2e4. \ub2e8\u00a0enable\ub418\uace0 \ub098\uc11c\ub3c4\u00a0\u00a0load\uc5d0 \uc758\ud574 \uc785\ub825\ub41c\u00a0data\uc5d0 \ub300\ud574\u00a0trigger\uac00\u00a0fire\ub418\uc9c0\ub294 \uc54a\ub294\ub2e4.<\/p>\n

 <\/p>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n<\/div>\n","protected":false},"excerpt":{"rendered":"

\ub9e4\uc6b0 \ub9ce\uc740 \uc591\uc758 \ub370\uc774\ud0c0\ub97c \ube60\ub978 \uc2dc\uac04 \ub0b4\uc5d0\u00a0load\ud558\uace0\uc790\ud558\ub294 \uacbd\uc6b0\u00a0direct path load\ub97c \uc0ac\uc6a9\ud560 \uc218 \uc788\ub2e4. \uc5ec\uae30\uc5d0\uc11c \uc774\ub7ec\ud55c\u00a0direct path load\uc758 \uc790\uc138\ud55c \uac1c\ub150 \ubc0f \uc0ac\uc6a9\ubc29\ubc95,\uc0ac\uc6a9 \uc2dc \uace0\ub824\ud574\uc57c \ud560 \uc810 \ub4f1\uc744 \uc124\uba85\ud55c\ub2e4.   1. conventional path load \uc77c\ubc18\uc801\uc778\u00a0sql*loader\ub97c \uc774\uc6a9\ud55c \ubc29\ubc95\uc740 \uc874\uc7ac\ud558\ub294\u00a0table\uc5d0\u00a0datafile\ub0b4\uc758\u00a0data\ub97c\u00a0\u00a0SQL\uc758\u00a0INSERT command\ub97c \uc774\uc6a9\ud558\uc5ec\u00a0insert\uc2dc\ud0a8\ub2e4. \uc774\ub807\uac8c\u00a0SQL command\ub97c \uc774\uc6a9\ud558\uae30 \ub54c\ubb38\uc5d0 \uac01\uac01\uc758 \ub370\uc774\ud0c0\ub97c \uc704\ud55c\u00a0insert command\uac00 \uc0dd\uc131\ub418\uc5b4\u00a0parsing\ub418\ub294 \uacfc\uc815\uc774 \ud544\uc694\ud558\uba70, \uba3c\uc800\u00a0bind array buffer (data block buffer)\ub0b4\uc5d0\u00a0insert\ub418\ub294 […]<\/p>\n","protected":false},"author":1,"featured_media":2875,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_bbp_topic_count":0,"_bbp_reply_count":0,"_bbp_total_topic_count":0,"_bbp_total_reply_count":0,"_bbp_voice_count":0,"_bbp_anonymous_reply_count":0,"_bbp_topic_count_hidden":0,"_bbp_reply_count_hidden":0,"_bbp_forum_subforum_count":0,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"spay_email":""},"categories":[9],"tags":[93,95],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/1-know-how.jpg?fit=937%2C450","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2892"}],"collection":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcomments&post=2892"}],"version-history":[{"count":1,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2892\/revisions"}],"predecessor-version":[{"id":2893,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/2892\/revisions\/2893"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/2875"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=2892"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=2892"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=2892"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}