{"id":1544,"date":"2017-09-02T10:09:19","date_gmt":"2017-09-02T02:09:19","guid":{"rendered":"http:\/\/coderbee.net\/?p=1544"},"modified":"2017-09-02T10:09:19","modified_gmt":"2017-09-02T02:09:19","slug":"append-hint-direct-path-insert","status":"publish","type":"post","link":"https:\/\/coderbee.net\/index.php\/db\/20170902\/1544","title":{"rendered":"append hint, direct-path insert"},"content":{"rendered":"<p>\u4e00\u4efd\u7b14\u8bb0\u3002<\/p>\n<h1>1. append  hint<\/h1>\n<p>\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165(direct load insert, direct-path insert)\u662f\u8fd0\u884c insert \u8bed\u53e5\u7684\u4e00\u79cd\u5feb\u901f\u65b9\u6cd5\u3002\u5bf9\u4e8e\u52a0\u8f7d\u5927\u91cf\u6570\u636e\u884c\u7279\u522b\u6709\u7528\u3002<\/p>\n<h2>1.1 append hint \u5982\u4f55\u5f71\u54cd\u6027\u80fd<\/h2>\n<ul>\n<li>\u6570\u636e\u88ab\u8ffd\u52a0\u5230\u8868\u7684\u672b\u5c3e\uff0c\u800c\u4e0d\u662f\u5c1d\u8bd5\u4f7f\u7528\u8868\u91cc\u5df2\u5b58\u5728\u7684\u7a7a\u95f2\u7a7a\u95f4\u3002<\/li>\n<li>\u6570\u636e\u88ab\u76f4\u63a5\u5199\u5230\u6570\u636e\u6587\u4ef6\uff0c\u907f\u5f00\u4e86 \uff08\u5199,buffer\uff09\u7f13\u51b2\u3001\uff08\u8bfb,cache\uff09\u7f13\u5b58\u3002<\/li>\n<li>\u5f15\u7528\u5b8c\u6574\u6027\u7ea6\u675f\u5c06\u4e0d\u4f1a\u8003\u8651\u3002<\/li>\n<li>\u89e6\u53d1\u5668\u7684\u5904\u7406\u5c06\u4e0d\u4f1a\u6267\u884c\u3002<\/li>\n<\/ul>\n<p>\u540e\u9762\u4e24\u70b9\u53ef\u80fd\u5bfc\u81f4\u6570\u636e\u903b\u8f91\u635f\u5316\uff0c\u56e0\u6b64\uff0c\u5982\u679c\u8868\u4e0a\u5141\u8bb8\u5f15\u7528\u5b8c\u6574\u6027\u7ea6\u675f\u548c\u89e6\u53d1\u5668\uff0cOracle \u5ffd\u7565 <code>append<\/code> hint \u5e76\u4ee5\u4f20\u7edf\u7684 insert \u65b9\u5f0f\u52a0\u8f7d\u6570\u636e\u3002<\/p>\n<h3>1.1.1 append hint \u5bf9\u8868\u5927\u5c0f\u7684\u5f71\u54cd\uff08\u9ad8\u6c34\u4f4d\u7ebf, high water mark\uff09<\/h3>\n<p>\u7531\u4e8e\u76f4\u63a5\u8def\u5f84\u63d2\u5165\u628a\u6570\u636e\u8ffd\u52a0\u5230\u8868\u7684\u672b\u5c3e\uff0c\u5b83\u4eec\u4e0d\u65ad\u5730\u589e\u52a0\u8868\u7684\u9ad8\u6c34\u4f4d\u7ebf\uff0c\u5373\u4f7f\u8868\u91cc\u8fd8\u6709\u5f88\u591a\u7a7a\u95f2\u7a7a\u95f4\u3002<code>append<\/code> hint \u53ef\u80fd\u5bfc\u81f4\u5f88\u5927\u7684\u8868\u91cc\u5305\u542b\u4e86\u5f88\u591a\u7a00\u758f\u586b\u5145\u7684\u5757\u3002\u8fd9\u53ef\u4ee5\u901a\u8fc7\u4e0b\u9762\u7684\u6536\u7f29\u64cd\u4f5c\u6765\u7ba1\u7406\uff1a<\/p>\n<ul>\n<li>\u5bfc\u51fa\u6570\u636e\u3001 truncate \u8868\u7136\u540e\u5bfc\u5165\u6570\u636e\u3002<\/li>\n<li>\u4f7f\u7528 <code>create table ... as select<\/code>(CTAS) \u64cd\u4f5c\u6765\u6784\u5efa\u65b0\u7684\u8868\uff0c\u8ba9\u6570\u636e\u538b\u7f29\uff0c\u5220\u9664\u539f\u59cb\u8868\uff0c\u91cd\u547d\u540d\u65b0\u8868\u6765\u66ff\u4ee3\u539f\u59cb\u7684\u3002<\/li>\n<li>\u4f7f\u7528 <a href=\"https:\/\/oracle-base.com\/articles\/9i\/high-availability-enhancements-9i#OnlineTableRedefinition\">online table redefinition<\/a> \u64cd\u4f5c\u6765\u91cd\u65b0\u521b\u5efa\u8868\u3002<\/li>\n<li>\u4f7f\u7528 <a href=\"https:\/\/oracle-base.com\/articles\/10g\/space-object-transaction-management-10g#online_segment_shrink\">online segment shrink<\/a> \u64cd\u4f5c\u538b\u7f29\u6570\u636e\u3002<\/li>\n<\/ul>\n<h3>1.1.2 How the APPEND Hint Affects Redo Generation<\/h3>\n<p>If the database is running on NOARCHIVELOG mode, using just the APPEND hint will reduce redo generation. In reality, you will rarely run OLTP databases in NOARCHIVELOG mode, so what happens in ARCHIVELOG mode? In ARCHIVELOG mode, using the APPEND hint will not reduce redo generation unless the table is set to NOLOGGING.<\/p>\n<p><!--more--><\/p>\n<h2>1.2 \u526f\u4f5c\u7528<\/h2>\n<p>\u6709\u5982\u4e0b\u7684\u526f\u4f5c\u7528\uff1a<\/p>\n<ul>\n<li>\u6570\u636e\u88ab\u8ffd\u52a0\u5230\u8868\u7684\u672b\u5c3e\u3002<em>\u5df2\u5b58\u5728\u7684\u672a\u4f7f\u7528\u7a7a\u95f4\u4e0d\u4f1a\u88ab\u91cd\u7528<\/em>\u3002\u5982\u679c\u4f60\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u5927\u91cf\u884c\uff0c\u5220\u9664\u8fd9\u4e9b\u884c\uff0c\u7136\u540e\u518d\u6b21\u63d2\u5165\uff0c\u8fd9\u4e9b\u88ab\u5220\u9664\u884c\u7684\u7a7a\u95f4\u4e0d\u4f1a\u518d\u88ab\u91cd\u7528\u3002\u5982\u679c\u4f60\u5148 <code>truncate<\/code> \u8868\u6216\u5206\u533a\uff0c\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u5c06\u91cd\u7528\u5df2\u5b58\u5728\u7684\u7a7a\u95f2\u7a7a\u95f4\u3002<\/li>\n<li><strong>\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u53ea\u9002\u7528\u4e8e <code>insert into ... select ...<\/code><\/strong>\u3002\u5e26\u6709 <code>values<\/code> \u5b50\u53e5\u7684 <code>insert<\/code> \u5c06\u4f7f\u7528\u5e38\u89c4\u7684 <code>insert<\/code> \u3002<\/li>\n<li>\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u4f7f\u7528\u56de\u6eda\u6bb5\u6765\u7ef4\u62a4\u88ab\u52a0\u8f7d\u6570\u636e\u7684\u7d22\u5f15\u3002\u56de\u6eda\u6bb5\u7684\u5927\u5c0f\u5c06\u9650\u5236 <code>insert<\/code> \u7684\u5927\u5c0f\u3002\u5728\u52a0\u8f7d\u524d <a href=\"http:\/\/www.orafaq.com\/tuningguide\/rebuild%20index.html\">\u5220\u9664\u6216 invalidate<\/a> \u7d22\u5f15\u53ef\u4ee5\u907f\u5f00\u8fd9\u4e2a\u95ee\u9898\uff0c\u6216\u4f7f\u7528 <a href=\"http:\/\/www.orafaq.com\/tuningguide\/rowid%20range.html\">rowid range<\/a> \u6280\u672f\u6765\u7ed5\u8fc7\u5b83\u3002<\/li>\n<li>\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u4e0e <code>nologging<\/code> \u9009\u9879\u4e00\u8d77\u4f7f\u7528\u53ef\u4ee5\u8ba9\u6267\u884c\u66f4\u5feb\u3002\u8fd9\u610f\u5473\u7740\uff0c\u6570\u636e\u5e93\u4ece\u7cfb\u7edf\u5d29\u6e83\u4e8b\u4ef6\u4e2d\u6062\u590d\u5230 insert \u76f4\u884c\u524d\uff0c\u7136\u540e\u7ee7\u7eed\u5f80\u524d\uff0c\u9700\u8981\u91cd\u65b0\u6267\u884c insert \u3002<\/li>\n<li><strong>\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u4f1a\u4ee5\u72ec\u5360\u6a21\u5f0f\u9501\u4f4f\u8868\uff0c\u5176\u4ed6\u4f1a\u8bdd\u4e0d\u80fd\u63d2\u5165\u3001\u66f4\u65b0\u3001\u5220\u9664\u6570\u636e\u6216\u7ef4\u62a4\u4efb\u4f55\u7d22\u5f15\u3002<\/strong><\/li>\n<li>\u5728\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u540e\uff0c\u6267\u884c insert \u7684\u4f1a\u8bdd\u53ef\u4ee5\u5728\u540c\u4e00\u5f20\u8868\u4e0a\u6267\u884c\u5176\u4ed6\u7684\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\uff0c\u4f46\u4e0d\u80fd\u5728\u8868\u4e0a\u6267\u884c\u4efb\u4f55\u5176\u4ed6\u7684\u52a8\u4f5c\uff08<code>select<\/code>, <code>update<\/code>, <code>delete<\/code>, \u5e38\u89c4 <code>insert<\/code>\uff09\uff0c\u76f4\u5230\u4e8b\u52a1\u88ab\u63d0\u4ea4\u3002<\/li>\n<li>\u5f15\u7528\u5b8c\u6574\u6027\uff08referential integrity(foreign key)\uff09 \u7ea6\u675f\u548c\u89e6\u53d1\u5668\u5fc5\u987b\u5728\u8fd0\u884c\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u524d\u7981\u7528\u3002<\/li>\n<li>\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\u4e0d\u80fd\u53d1\u751f\u5728\uff1a<br \/>\n> * \u7d22\u5f15\u7ec4\u7ec7\u8868<br \/>\n> * \u6709 CLOB\/Object \u5217\u7684\u8868\uff1b<br \/>\n> * \u805a\u7c07\u8868<\/li>\n<\/ul>\n<h2>1.3 \u4f7f\u7528<\/h2>\n<pre><code class=\"sql\">INSERT \/*+ APPEND*\/\nINTO my_table\nSELECT * FROM my_other_table\n<\/code><\/pre>\n<p>\u4e3a\u4e86\u786e\u4fdd SQL \u4f7f\u7528\u4e86\u76f4\u63a5\u52a0\u8f7d\u63d2\u5165\uff0c\u5728<a href=\"http:\/\/www.orafaq.com\/tuningguide\/explain%20plan.html\">\u6267\u884c\u8ba1\u5212<\/a>\u91cc\u8fd0\u884c\uff0c\u5c06\u770b\u5230 <code>LOAD AS SELECT<\/code> \u3002<\/p>\n<p>\u4e00\u822c\u60c5\u51b5\u4e0b\uff0c\u5c3d\u91cf\u7528\u5e38\u89c4\u7684 insert \u3002\u5728\u8fd9\u4e2a<a href=\"https:\/\/asktom.oracle.com\/pls\/apex\/f?p=100:11:0::::P11_QUESTION_ID:228657900346252297\">\u95ee\u7b54<\/a>\u91cc\uff0cTom Kyte\u8ba4\u4e3a *150,000 is a very small number of records. * \u3002<\/p>\n<h1>append_values hint<\/h1>\n<p>append_values hint \u662f Oracle 11g R2 \u5f15\u5165\u7684\u3002\u5141\u8bb8\u6211\u4eec\u5728\u4f7f\u7528\u5e26\u6709 values \u5b50\u53e5\u7684 insert \u8bed\u53e5\u65f6\u5229\u7528\u76f4\u63a5\u8def\u5f84\u63d2\u5165\u7684\u4f18\u70b9\u3002\u4e00\u822c\u5730\uff0c\u6211\u4eec\u5e0c\u671b\u53ea\u5728 insert \u8bed\u53e5\u662f <code>forall<\/code> \u8bed\u53e5\u7684 <a href=\"https:\/\/oracle-base.com\/articles\/9i\/bulk-binds-and-record-processing-9i\">bulk \u64cd\u4f5c<\/a>\u7684\u4e00\u90e8\u5206\u65f6\u4f7f\u7528\u8be5 hint \u3002<\/p>\n<pre><code class=\"sql\">FORALL i IN 1..numrecords\n  INSERT \/*+ APPEND_VALUES *\/ INTO orderdata \n  VALUES(ordernum(i), custid(i), orderdate(i),shipmode(i), paymentid(i));\nCOMMIT;\n<\/code><\/pre>\n<p>\u8be5 hin \u76ee\u524d\u4e0d\u80fd\u8ddf <code>SAVE EXCEPTIONS<\/code> \u4e00\u8d77\u4f7f\u7528\u3002<\/p>\n<h1>\u53c2\u8003\u8d44\u6599<\/h1>\n<ul>\n<li><a href=\"http:\/\/www.orafaq.com\/tuningguide\/direct%20path.html\">Direct Load Insert<\/a><\/li>\n<li><a href=\"https:\/\/docs.oracle.com\/cd\/B10501_01\/server.920\/a96524\/c21dlins.htm\">Direct-Path INSERT<\/a><\/li>\n<li><a href=\"https:\/\/oracle-base.com\/articles\/misc\/append-hint\">APPEND Hint<\/a><\/li>\n<li><a href=\"https:\/\/oracle-base.com\/articles\/11g\/append-values-hint-11gr2\">APPEND_VALUES Hint in Oracle Database 11g Release 2<\/a><\/li>\n<li><a href=\"http:\/\/docs.oracle.com\/cd\/E18283_01\/server.112\/e17120\/tables004.htm#i1009864\">About Direct-Path INSERT<\/a><\/li>\n<\/ul>\n<hr\/>\n<p>\u6b22\u8fce\u5173\u6ce8\u6211\u7684\u5fae\u4fe1\u516c\u4f17\u53f7: <strong>coderbee\u7b14\u8bb0<\/strong>\uff0c\u53ef\u4ee5\u66f4\u53ca\u65f6\u56de\u590d\u4f60\u7684\u8ba8\u8bba\u3002<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" width=\"258\" height=\"258\" src=\"https:\/\/coderbee.net\/wp-content\/uploads\/2019\/01\/coderbee-note.jpg\" class=\"alignnone size-full wp-image-1707\"><\/p>\n","protected":false},"excerpt":{"rendered":"<p>\u4e00\u4efd\u7b14\u8bb0\u3002 1. append hint \u76f4\u63a5\u52a0\u8f7d\u63d2\u5165(direct load  &hellip; <a href=\"https:\/\/coderbee.net\/index.php\/db\/20170902\/1544\">\u7ee7\u7eed\u9605\u8bfb <span class=\"meta-nav\">&rarr;<\/span><\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[23],"tags":[282,283,281,284,99],"_links":{"self":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/1544"}],"collection":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/comments?post=1544"}],"version-history":[{"count":1,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/1544\/revisions"}],"predecessor-version":[{"id":1545,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/posts\/1544\/revisions\/1545"}],"wp:attachment":[{"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/media?parent=1544"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/categories?post=1544"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/coderbee.net\/index.php\/wp-json\/wp\/v2\/tags?post=1544"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}