{"id":4072,"date":"2018-08-01T09:50:48","date_gmt":"2018-08-01T00:50:48","guid":{"rendered":"http:\/\/haisins.epac.to\/wordpress\/?p=4072"},"modified":"2018-08-01T10:10:49","modified_gmt":"2018-08-01T01:10:49","slug":"db-link%eb%a5%bc-%ec%82%ac%ec%9a%a9%ed%95%9c-%eb%b6%84%ec%82%b0-%ec%bf%bc%eb%a6%ac%ec%9d%98-%ed%8a%9c%eb%8b%9d","status":"publish","type":"post","link":"http:\/\/haisins.synology.me\/wordpress\/?p=4072","title":{"rendered":"DB LINK\ub97c \uc0ac\uc6a9\ud55c \ubd84\uc0b0 \ucffc\ub9ac\uc758 \ud29c\ub2dd"},"content":{"rendered":"
\n

DB LINK\ub97c \uc774\uc6a9\ud55c \ubd84\uc0b0 DB Query\uc758 \uacbd\uc6b0, \ub85c\uceec DB \uc640 \ub2e4\ub978 \uad00\uc810\uc758 \ud29c\ub2dd\uc774 \ud544\uc694\ud569\ub2c8\ub2e4.<\/strong><\/p>\n

 <\/p>\n

1. \uac00\uae09\uc801 Nested Loop\uc744 \ud53c\ud558\uace0, Hash Join \ucc98\ub9ac \ud558\uc5ec, Network Round Trip \uc744 \uc904\uc778\ub2e4\u00a0\u00a0 <\/strong><\/p>\n

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<\/strong>
\n\uc0c1\ud669\uc5d0 \ub530\ub77c \ub2e4\ub974\uaca0\uc9c0\ub9cc \uc77c\ubc18\uc801\uc73c\ub85c \ub124\ud2b8\uc6cd\uc744 \ud1b5\ud55c \ubd84\uc0b0 SQL \ud29c\ub2dd\uc5d0\uc11c, <\/strong><\/span><\/p>\n

\ub85c\uceec DB\uc5d0\uc11c\ub9cc\u00a0<\/strong><\/span>\uc218\ud589\ub418\ub294 SQL\uacfc \ud29c\ub2dd\ud3ec\uc778\ud2b8\uac00 \ub2e4\ub978 \uc810\uc740…<\/strong><\/span><\/p>\n

\ubd84\uc0b0 DB QUERY\uc758 NETED LOOP \uc870\uc778\uc740 \uc870\uc778 \uac74\uc218\ub9cc\ud07c\uc758 \ub124\ud2b8\uc6cd RoundTrip\uc774 \ubc1c\uc0dd\ud558\ubbc0\ub85c,\u00a0<\/strong><\/span>\uc870\uc778\uac74\uc218\uac00 \ub9ce\uc744 \uacbd\uc6b0<\/strong><\/span><\/p>\n

\ub124\ud2b8\uc6cd Round Trip \uc5d0 \uc18c\uc694\ub418\ub294 \uc2dc\uac04\uc774 \uc0c1\ub2f9\ud788 \ub9ce\ub2e4\ub294 \uac83\uc785\ub2c8\ub2e4.<\/strong><\/span><\/p>\n

\uadf8\ub7ec\ubbc0\ub85c Nested Loop\uc870\uc778\uc73c\ub85c \ud480\ub9ac\uc9c0 \uc54a\uc73c\uba74 \uc218\ubc31\ub9cc\uac74\uc774\uc0c1\uc758 \ud14c\uc774\ube14\uc5d0 \ub300\ud55c Full Table\u00a0<\/strong><\/span>Scan\ub4f1\uc774 \ubc1c\uc0dd\ud558\ub294 \uacbd\uc6b0\uac00\u00a0\uc544\ub2c8\ub77c\uba74, <\/strong><\/span><\/p>\n

\ub418\ub3c4\ub85d\uc774\uba74 Sort-Merge\ub098 Hash Join\uc73c\ub85c \uc218\ud589\ub418\ub3c4\ub85d\u00a0<\/strong><\/span>PLAN\uc744 \uc870\uc815\ud558\uc5ec, \uc870\uc778\uc73c\ub85c \uc778\ud55c Network Roundtrip\uc744\u00a0\uac00\uae09\uc801 \uc904\uc774\ub3c4\ub85d \ud569\ub2c8\ub2e4.<\/strong><\/span><\/p>\n<\/div>\n

 <\/p>\n

\n

2. Driving_Site \ud78c\ud2b8\ub97c \uc774\uc6a9\ud558\uc5ec, \uc77d\ud600\uc9c0\ub294 \uc77c\ub7c9\uc774 \ub9ce\uc740 DB\uac00 SQL \uc218\ud589\uc758 \uc8fc\uccb4\uac00 \ub418\ub3c4\ub85d \ud55c\ub2e4.<\/strong>
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<\/strong>
\n\uc608\ub97c\ub4e4\uc5b4 Remote \uc0ac\uc774\ud2b8\uc758 \uacb0\uacfc\uac74\uc218\uac00 100\ub9cc\uac74\uc9dc\ub9ac\uc778 \ud14c\uc774\ube14\uacfc \ub85c\uceec\uc758 100\uac74\uc9dc\ub9ac\u00a0<\/strong>\ucf54\ub4dc \ud14c\uc774\ube14\uc774 \uc870\uc778\ub420 \uacbd\uc6b0\u00a0<\/strong><\/span><\/p>\n

Driving_SITE\ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\uc5ec, 100\ub9cc\uac74\uc9dc\ub9ac \ud14c\uc774\ube14\uc774 \uc704\uce58\ud55c\u00a0<\/strong>DB\uac00 \uc870\uc778\uc218\ud589\uc758 \uc8fc\uccb4\uac00 \ub418\ub3c4\ub85d \ud569\ub2c8\ub2e4.<\/strong><\/span><\/p>\n

 <\/p>\n

3. \ubc14\uc778\ub4dc\ubcc0\uc218\ub098 \ubb38\uc790\uc5f4\uac12\uc758 \uc9c1\uc811 \uc0ac\uc6a9\uc740 PLAN\uc744 \uace0\uc815\uc2dc\ud0a4\uac8c \ub41c\ub2e4.<\/strong>
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<\/strong>
\n2\ubc88\uacfc \uac19\uc774 Driving_Site \ud78c\ud2b8\ub97c \uc774\uc6a9\ud558\uc5ec SQL \uc218\ud589 \uc8fc\uccb4\uac00 \ub418\ub294 DB\ub97c \uc9c0\uc815\ud558\uc5ec,\u00a0<\/strong><\/span>PLAN\uc744 \uc870\uc815 \ud558\ub294 \uacbd\uc6b0\uc5d0 <\/strong><\/span><\/p>\n

SQL\uc5d0 \ubc14\uc778\ub4dc \ubcc0\uc218 \ub098 \uc9c1\uc811\uc801\uc778 \ubb38\uc790\uc5f4 \uac12\uc774 \uc788\ub294 \uacbd\uc6b0 \ud78c\ud2b8\uac00\u00a0<\/strong><\/span>\uc6d0\ud558\ub294\ub300\ub85c \uc801\uc6a9\ub418\uc9c0 \uc54a\uc2b5\ub2c8\ub2e4.<\/strong><\/span><\/p>\n

\uc989, SQL\uc5d0 SELECT-LIST\uc5d0 \ubb38\uc790\uc5f4\uac12 \uc774\ub098 \ubc14\uc778\ub4dc \ubcc0\uc218 \uac12\uc774 \uc788\uc73c\uba74, PLAN\uc0c1 Remote\uc5d0\uc11c\u00a0<\/strong><\/span>\uc218\ud589\uc774 \ub418\uc9c0 \uc54a\uace0\u00a0 \ud56d\uc0c1 \ub85c\uceec\uc5d0\uc11c \uc218\ud589\uc774 \ub429\ub2c8\ub2e4.<\/strong><\/span><\/p>\n

\uc774 \uacbd\uc6b0\uc5d0\ub294 \ubb38\uc790\uc5f4\uc774\ub098 \ubc14\uc778\ub4dc \ubcc0\uc218\uac12\uc744 \uc81c\uc678\ud55c SQL\uc744 \uc778\ub77c\uc778 \ubdf0\uc5d0\uc11c \uc218\ud589\ud558\uc5ec,\u00a0<\/strong><\/span>Remote DB\uc5d0\uc11c \ud574\ub2f9 SQL\uc774 <\/strong><\/span><\/p>\n

\uc218\ud589\ub418\ub3c4\ub85d \ud558\uace0, \uc778\ub77c\uc778 \ubdf0 \ubc16\uc5d0\uc11c \ud544\uc694\ud55c \ubb38\uc790\uc5f4 \uac12\uc744 \uc8fc\uace0,\u00a0<\/strong><\/span>NO_MERGE \ud78c\ud2b8\ub97c \uc0ac\uc6a9\ud558\ub3c4\ub85d \ud569\ub2c8\ub2e4.<\/strong><\/span><\/p>\n

 <\/p>\n

\uc608><\/strong><\/p>\n

INSERT INTO T3\r\nSELECT \/*+ DRIVING_SITE(T1) *\/\r\n\u2018ADD_COLUMN\u2019, T1.*, T2.*\r\nFROM T1@LINK1 T1, T1@LINK1 T2\r\nWHERE A.COL1 = B.COL2;\r\n\r\nPLAN:\r\nExecution Plan\r\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013\r\n0      INSERT STATEMENT Optimizer=CHOOSE (Cost=5 Card=3K Bytes=41K)\r\n1    0   VIEW (Cost=5 Card=3K Bytes=41K)\r\n2    1     HASH JOIN (Cost=5 Card=3K Bytes=20K)\r\n3    2       REMOTE (Cost=2 Card=3K Bytes=10K) [LINK1] (SERIAL_FROM_REMOTE)\r\n4    2       REMOTE (Cost=2 Card=3K Bytes=10K) [LINK1] (SERIAL_FROM_REMOTE)<\/pre>\n
\uc774\uacbd\uc6b0\ub294.., DRIVING_SITE \ud78c\ud2b8\ub97c \uc918\ub3c4 \ub9ac\ubaa8\ud2b8 DB\uc5d0\uc11c T1\uacfc T2\ud14c\uc774\ube14\uc744 \ub85c\uceec DB\ub85c \uc77d\uc5b4\uc640\uc11c\u00a0<\/strong><\/span>\ub85c\uceec\uc5d0\uc11c \uc870\uc778\ud558\uac8c \ub429\ub2c8\ub2e4.<\/strong><\/span><\/p>\n
\uc774\uac83\uc740 \ub9ac\ubaa8\ud2b8DB\uc5d0\uc11c \uc870\uc778\uc774 \ubc1c\uc0dd\ud574\uc11c \uacb0\uacfc\uac12\ub9cc \ubc1b\ub294 \uac83\uc774 \uc77c\ubc18\uc801\uc73c\ub85c \ud6e8\uc52c \uc720\ub9ac\ud569\ub2c8\ub2e4.<\/strong><\/span><\/p>\n
\uc544\ub798\uc640\uac19\uc774 \ubb38\uc790\uc5f4\uac12\uc744 \ubc16\uc73c\ub85c \ube80 \uc778\ub77c\uc778\ubdf0\uc640 NO_MERGE \ud78c\ud2b8\ub97c \uc774\uc6a9\ud558\uc5ec \uc6d0\ud558\ub294 PLAN\uc73c\ub85c\u00a0<\/strong><\/span>\uc218\uc815\uc774 \uac00\ub2a5\ud569\ub2c8\ub2e4.<\/strong><\/span><\/p>\n
 <\/p>\n
INSERT INTO T3\r\nSELECT \/*+ NO_MERGE(A) *\/ \u2018ADD_COLUMN\u2019, A.*\r\nFROM (\r\nSELECT \/*+ DRIVING_SITE(A) *\/\r\nT1.*, T2.*\r\nFROM T1@LINK1 T1, T1@LINK1 T2\r\nWHERE A.COL1 = B.COL2) A;\r\n\r\nPLAN:\r\nExecution Plan\r\n\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2014\u2013\r\n0      INSERT STATEMENT Optimizer=CHOOSE (Cost=5 Card=100 Bytes=400)\r\n1    0   VIEW (Cost=5 Card=100 Bytes=400)\r\n2    1     REMOTE [LINK1] (SERIAL_FROM_REMOTE)<\/pre>\n<\/div>\n
 <\/p>\n
\n
4. \ubdf0\ub97c \uc774\uc6a9\ud558\uc5ec, PLAN\uc744 \uc870\uc815\ud55c\ub2e4.<\/strong>
\n~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~<\/strong>
\n3\ubc88\uacfc \uac19\uc740 \ubc29\ubc95\uc744 \uc0ac\uc6a9\ud574\ub3c4 \ub418\uace0 \ubdf0\ub97c \uc774\uc6a9\ud574\ub3c4 \ub429\ub2c8\ub2e4.<\/strong><\/span><\/p>\n
REMOTE \uc0ac\uc774\ud2b8\uc758 \ud14c\uc774\ube14 \uc5ec\ub7ec\uac1cRK \uc870\uc778\ub420 \uacbd\uc6b0 \ud574\ub2f9 SQL\uc744 \ud574\ub2f9 \ub9ac\ubaa8\ud2b8\uc0ac\uc774\ud2b8\uc5d0\u00a0<\/strong><\/span>\ubdf0\ub97c \ub9cc\ub4e4\uc5b4 \ub193\ub294\ub2e4\uba74, \ud55c\ubc88\uc758 Remote Operation\ub9cc\uc774 \uc774\ub8e8\uc5b4\uc9c8 \uac83\uc785\ub2c8\ub2e4.<\/strong><\/span><\/p>\n
\uc989, Driving_site \ub780 \ud78c\ud2b8\uac00 \uc81c\ub300\ub85c \uc218\ud589\uc774 \ub418\uc9c0 \uc54a\ub294 \uacbd\uc6b0 \uc218\ud589\uc758 \uc8fc\uccb4\uac00 \ub418\uae30\ub97c \uc6d0\ud558\ub294\u00a0<\/strong><\/span>Remote DB\uc0c1\uc5d0 View\ub97c \uc0dd\uc131\ud558\uc5ec \ud574\ub2f9 View \ub97c SELECT \ud558\uc5ec PLAN\uc744 \uc870\uc815\ud560 \uc218 \uc788\uc2b5\ub2c8\ub2e4.<\/strong><\/span><\/p>\n<\/div>\n","protected":false},"excerpt":{"rendered":"
DB LINK\ub97c \uc774\uc6a9\ud55c \ubd84\uc0b0 DB Query\uc758 \uacbd\uc6b0, \ub85c\uceec DB \uc640 \ub2e4\ub978 \uad00\uc810\uc758 \ud29c\ub2dd\uc774 \ud544\uc694\ud569\ub2c8\ub2e4.   1. \uac00\uae09\uc801 Nested Loop\uc744 \ud53c\ud558\uace0, Hash Join \ucc98\ub9ac \ud558\uc5ec, Network Round Trip \uc744 \uc904\uc778\ub2e4\u00a0\u00a0 ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ \uc0c1\ud669\uc5d0 \ub530\ub77c \ub2e4\ub974\uaca0\uc9c0\ub9cc \uc77c\ubc18\uc801\uc73c\ub85c \ub124\ud2b8\uc6cd\uc744 \ud1b5\ud55c \ubd84\uc0b0 SQL \ud29c\ub2dd\uc5d0\uc11c, \ub85c\uceec DB\uc5d0\uc11c\ub9cc\u00a0\uc218\ud589\ub418\ub294 SQL\uacfc \ud29c\ub2dd\ud3ec\uc778\ud2b8\uac00 \ub2e4\ub978 \uc810\uc740… \ubd84\uc0b0 DB QUERY\uc758 NETED LOOP \uc870\uc778\uc740 \uc870\uc778 \uac74\uc218\ub9cc\ud07c\uc758 \ub124\ud2b8\uc6cd RoundTrip\uc774 […]<\/p>\n","protected":false},"author":1,"featured_media":3402,"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":[11],"tags":[1120,894,893,1119,1026],"jetpack_featured_media_url":"https:\/\/i0.wp.com\/haisins.synology.me\/wordpress\/wp-content\/uploads\/2018\/02\/1-know-how-1.jpg?fit=937%2C450","amp_enabled":true,"_links":{"self":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4072"}],"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=4072"}],"version-history":[{"count":9,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4072\/revisions"}],"predecessor-version":[{"id":4081,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/posts\/4072\/revisions\/4081"}],"wp:featuredmedia":[{"embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=\/wp\/v2\/media\/3402"}],"wp:attachment":[{"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fmedia&parent=4072"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Fcategories&post=4072"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/haisins.synology.me\/wordpress\/index.php?rest_route=%2Fwp%2Fv2%2Ftags&post=4072"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}