{"id":4759,"date":"2015-05-18T12:27:57","date_gmt":"2015-05-18T10:27:57","guid":{"rendered":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/"},"modified":"2015-05-18T12:27:57","modified_gmt":"2015-05-18T10:27:57","slug":"variations-on-1m-rows-insert-4-append_values","status":"publish","type":"post","link":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/","title":{"rendered":"Variations on 1M rows insert (4): APPEND_VALUES"},"content":{"rendered":"

By Franck Pachot<\/h2>\n

.
\nIn the first variation<\/a> I did a bulk insert with the PL\/SQL FORALL statement. A comment suggests to add the APPEND_VALUES hint that appeared in 11gR2. APPEND_VALUES hint does the same than APPEND but it’s for inserts with VALUES clause: It inserts in direct-path – directly into the datafile, bypassing the buffer cache. Of course, this makes sense only with bulk insert because you don’t want to do that with only one row.<\/p>\n

APPEND_VALUES<\/h3>\n

So, from the previous test, inserting 1 million rows in only one transaction (no intermediate commits) into a table with only one index (primary key) took 46 seconds. With bulk insert, the time is only 7 seconds. Let’s do the same bulk, but with the APPEND_VALUES hint. I changed only the insert line:<\/p>\n

SQL> create table DEMO (\"id\" number primary key, \"text\" varchar2(15), \"number\" number) ;\nTable created.\n\nSQL> set timing on\nSQL> declare\n  2   type people_array is varray(12) of varchar(15);\n  3   people people_array := people_array( 'Marc', 'Bill', 'George', 'Eliot', 'Matt', 'Trey', 'Tracy','Greg', 'Steve', 'Kristina', 'Katie', 'Jeff' );\n  4   people_count number :=people.COUNT;\n  5   type DEMO_record_type is record (\"user_id\" number , \"name\" varchar2(15), \"number\" number) ;\n  6   type DEMO_collection_type is table of DEMO_record_type index by binary_integer;\n  7   DEMO_collection DEMO_collection_type;\n  8  begin\n  9   -- fill the collection\n 10   for i in 1..1e6 loop\n 11    DEMO_collection(i).\"user_id\":=i;\n 12    DEMO_collection(i).\"name\":=people( dbms_random.value(1,people.count) );\n 13    DEMO_collection(i).\"number\":=trunc(dbms_random.value(0,10000));\n 14    null;\n 15   end loop;\n 16   forall i in 1..DEMO_collection.count insert \/*+ append_values *\/ into DEMO values DEMO_collection(i);\n 17   commit;\n 18  end;\n 19  \/\n<\/code><\/pre>\n
and here is the execution time:<\/p>\n
PL\/SQL procedure successfully completed.\nElapsed: 00:00:05.28\n<\/code><\/pre>\n
It’s a bit faster. However this testcase is not very well suited to show the benefit.<\/p>\n
First, you must know that inserting in direct-path locks the table. We bypass the buffer cache and it’s the buffer cache that manages concurrency when updating the blocks. Only one session can modify a block, and that’s available also in RAC. So, when you bypass the buffer cache you must lock the table (or the partition if you insert into a partition) to be sure you are the only one that inserts after the high water mark.<\/p>\n
Now about the testcase. Here I insert into an empty table. The direct-path insert is very efficient for index maintenance because instead of maintaining index row-by-row, the insert is build afterwards (data inserted after high water mark is read and sorted) and merged to the existing index. This optimization gives better performance on an index that has an higher depth.<\/p>\n
APPEND<\/h3>\n
If you want to have an idea about how fast is direct-path insert, let’s see how long it takes to insert the 1 million rows into another table – identical, with a primary key. This is easy with CTAS:<\/p>\n
SQL> set timing on\nSQL> create table DEMO2 (\"id\" primary key , \"text\" , \"number\" ) as select * from DEMO;\nTable created.\nElapsed: 00:00:01.01\n<\/code><\/pre>\n
One second. That’s the fastest way to insert 1 million rows in batch when you have them in a table (or file through external table), when you can lock the whole table and you don’t need to do intermediate commits.
\nWe can also do the same with INSERT \/*+ APPEND *\/<\/p>\n
SQL> truncate table DEMO2 reuse storage;\nTable truncated.\nElapsed: 00:00:00.17\n\nSQL> insert \/*+ append *\/ into DEMO2 select * from DEMO;\n1000000 rows created.\nElapsed: 00:00:01.36\n<\/code><\/pre>\n
An it’s the same time (I’ll not compare when difference is less than one second).<\/p>\n
The index maintenance is fast, but you can go further by disabling the indexes (ALTER INDEX … UNUSABLE) before and rebuild them afterwards.<\/p>\n
However, my initial testcase was made thinking of OLTP, concurrent inserts from difference sessions. So direct-path writes is definitly not for that as it locks the table. But I wanted to show how 1 million rows is very small when we are doing thinks in bulk from one session.<\/p>\n","protected":false},"excerpt":{"rendered":"
By Franck Pachot . In the first variation I did a bulk insert with the PL\/SQL FORALL statement. A comment suggests to add the APPEND_VALUES hint that appeared in 11gR2. APPEND_VALUES hint does the same than APPEND but it’s for inserts with VALUES clause: It inserts in direct-path – directly into the datafile, bypassing the… <\/p>\n","protected":false},"author":27,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"neve_meta_sidebar":"","neve_meta_container":"","neve_meta_enable_content_width":"","neve_meta_content_width":0,"neve_meta_title_alignment":"","neve_meta_author_avatar":"","neve_post_elements_order":"","neve_meta_disable_header":"","neve_meta_disable_footer":"","neve_meta_disable_title":"","neve_meta_reading_time":"","_themeisle_gutenberg_block_has_review":false,"footnotes":""},"categories":[4133,59],"tags":[],"class_list":["post-4759","post","type-post","status-publish","format-standard","hentry","category-database","category-oracle"],"yoast_head":"\nVariations on 1M rows insert (4): APPEND_VALUES - dbi services | Blog<\/title>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Variations on 1M rows insert (4): APPEND_VALUES\" \/>\n<meta property=\"og:description\" content=\"By Franck Pachot . In the first variation I did a bulk insert with the PL\/SQL FORALL statement. A comment suggests to add the APPEND_VALUES hint that appeared in 11gR2. APPEND_VALUES hint does the same than APPEND but it’s for inserts with VALUES clause: It inserts in direct-path – directly into the datafile, bypassing the… \" \/>\n<meta property=\"og:url\" content=\"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/\" \/>\n<meta property=\"og:site_name\" content=\"dbi services | Blog\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/dbiservices\/\" \/>\n<meta property=\"article:published_time\" content=\"2015-05-18T10:27:57+00:00\" \/>\n<meta name=\"author\" content=\"Oracle Team\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:site\" content=\"@dbiservices\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Oracle Team\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"3 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/\"},\"author\":{\"name\":\"Oracle Team\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bf7c7eb0bec165183207237c9d0ee1d5\"},\"headline\":\"Variations on 1M rows insert (4): APPEND_VALUES\",\"datePublished\":\"2015-05-18T10:27:57+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/\"},\"wordCount\":481,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"articleSection\":[\"Database\",\"Oracle\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/\",\"name\":\"Variations on 1M rows insert (4): APPEND_VALUES - dbi services | Blog\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\"},\"datePublished\":\"2015-05-18T10:27:57+00:00\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/\"]}]},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/variations-on-1m-rows-insert-4-append_values\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Accueil\",\"item\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Variations on 1M rows insert (4): APPEND_VALUES\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#website\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"name\":\"dbi services Blog\",\"description\":\"#BeSharing\",\"publisher\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\"},\"alternateName\":\"dbi Blog\",\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#organization\",\"name\":\"dbi services\",\"alternateName\":\"dbi\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2026\\\/09\\\/dbi-services-logo.png\",\"width\":512,\"height\":512,\"caption\":\"dbi services\"},\"image\":{\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/logo\\\/image\\\/\"},\"sameAs\":[\"https:\\\/\\\/www.facebook.com\\\/dbiservices\\\/\",\"https:\\\/\\\/x.com\\\/dbiservices\",\"https:\\\/\\\/www.linkedin.com\\\/company\\\/dbi-services\\\/\",\"https:\\\/\\\/www.youtube.com\\\/user\\\/dbiservices\"],\"description\":\"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.\",\"email\":\"info@dbi-services.com\",\"telephone\":\"+41 32 422 96 00\",\"legalName\":\"dbi services SA\"},{\"@type\":\"Person\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/#\\\/schema\\\/person\\\/bf7c7eb0bec165183207237c9d0ee1d5\",\"name\":\"Oracle Team\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"contentUrl\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/wp-content\\\/uploads\\\/2022\\\/12\\\/oracle-square.png\",\"caption\":\"Oracle Team\"},\"description\":\"Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.\",\"url\":\"https:\\\/\\\/www.dbi-services.com\\\/blog\\\/author\\\/oracle-team\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Variations on 1M rows insert (4): APPEND_VALUES - dbi services | Blog","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/","og_locale":"en_US","og_type":"article","og_title":"Variations on 1M rows insert (4): APPEND_VALUES","og_description":"By Franck Pachot . In the first variation I did a bulk insert with the PL\/SQL FORALL statement. A comment suggests to add the APPEND_VALUES hint that appeared in 11gR2. APPEND_VALUES hint does the same than APPEND but it’s for inserts with VALUES clause: It inserts in direct-path – directly into the datafile, bypassing the… ","og_url":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/","og_site_name":"dbi services | Blog","article_publisher":"https:\/\/www.facebook.com\/dbiservices\/","article_published_time":"2015-05-18T10:27:57+00:00","author":"Oracle Team","twitter_card":"summary_large_image","twitter_creator":"@dbiservices","twitter_site":"@dbiservices","twitter_misc":{"Written by":"Oracle Team","Est. reading time":"3 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/#article","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/"},"author":{"name":"Oracle Team","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bf7c7eb0bec165183207237c9d0ee1d5"},"headline":"Variations on 1M rows insert (4): APPEND_VALUES","datePublished":"2015-05-18T10:27:57+00:00","mainEntityOfPage":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/"},"wordCount":481,"commentCount":0,"publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"articleSection":["Database","Oracle"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/","url":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/","name":"Variations on 1M rows insert (4): APPEND_VALUES - dbi services | Blog","isPartOf":{"@id":"https:\/\/www.dbi-services.com\/blog\/#website"},"datePublished":"2015-05-18T10:27:57+00:00","breadcrumb":{"@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/"]}]},{"@type":"BreadcrumbList","@id":"https:\/\/www.dbi-services.com\/blog\/variations-on-1m-rows-insert-4-append_values\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Accueil","item":"https:\/\/www.dbi-services.com\/blog\/"},{"@type":"ListItem","position":2,"name":"Variations on 1M rows insert (4): APPEND_VALUES"}]},{"@type":"WebSite","@id":"https:\/\/www.dbi-services.com\/blog\/#website","url":"https:\/\/www.dbi-services.com\/blog\/","name":"dbi services Blog","description":"#BeSharing","publisher":{"@id":"https:\/\/www.dbi-services.com\/blog\/#organization"},"alternateName":"dbi Blog","potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/www.dbi-services.com\/blog\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/www.dbi-services.com\/blog\/#organization","name":"dbi services","alternateName":"dbi","url":"https:\/\/www.dbi-services.com\/blog\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2026\/09\/dbi-services-logo.png","width":512,"height":512,"caption":"dbi services"},"image":{"@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/dbiservices\/","https:\/\/x.com\/dbiservices","https:\/\/www.linkedin.com\/company\/dbi-services\/","https:\/\/www.youtube.com\/user\/dbiservices"],"description":"Swiss IT consulting and services company specialised in efficient data infrastructures and platforms: databases, application servers, cloud, DevOps, digitalization, analytics, data quality and monitoring. Headquartered in Del\u00e9mont with offices in Nyon, Basel, Bern and Zurich.","email":"info@dbi-services.com","telephone":"+41 32 422 96 00","legalName":"dbi services SA"},{"@type":"Person","@id":"https:\/\/www.dbi-services.com\/blog\/#\/schema\/person\/bf7c7eb0bec165183207237c9d0ee1d5","name":"Oracle Team","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","url":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","contentUrl":"https:\/\/www.dbi-services.com\/blog\/wp-content\/uploads\/2022\/12\/oracle-square.png","caption":"Oracle Team"},"description":"Posts published under the Oracle Team account are collective contributions by the dbi services Oracle consultants. The practice covers the full Oracle Database lifecycle: architecture, migration and upgrade, high availability with RAC and Data Guard, backup and recovery, performance tuning, and engineered systems such as Oracle Database Appliance and Exadata. The team holds Oracle certifications up to Certified Master level and operates customer platforms under service level agreements.","url":"https:\/\/www.dbi-services.com\/blog\/author\/oracle-team\/"}]}},"_links":{"self":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4759","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/users\/27"}],"replies":[{"embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/comments?post=4759"}],"version-history":[{"count":0,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/posts\/4759\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/media?parent=4759"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/categories?post=4759"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.dbi-services.com\/blog\/wp-json\/wp\/v2\/tags?post=4759"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}