{"id":13983,"date":"2020-03-10T08:22:23","date_gmt":"2020-03-10T08:22:23","guid":{"rendered":"https:\/\/ittutorial.org\/?p=13983"},"modified":"2021-06-10T12:05:25","modified_gmt":"2021-06-10T12:05:25","slug":"bulk-insert-and-bulk-update-bulk-delete-in-oracle-database","status":"publish","type":"post","link":"https:\/\/ittutorial.org\/bulk-insert-and-bulk-update-bulk-delete-in-oracle-database\/","title":{"rendered":"Bulk Insert and Bulk Update in Oracle Database"},"content":{"rendered":"<p>I will explain Bulk Insert and Bulk Update in Oracle Database in this article.<\/p>\n<p><!--more--><\/p>\n<p>&nbsp;<\/p>\n<h2><span style=\"color: #ff0000;\">Bulk Insert and Bulk Update in Oracle<\/span><\/h2>\n<p>You can read the following article if you want to learn more details about Performance Tuning tips and Tricks.<\/p>\n<blockquote class=\"wp-embedded-content\" data-secret=\"a4VN4FDxO7\"><p><a href=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/\">SQL Tuning Tips and Tricks Tutorial in Oracle -2<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Tuning Tips and Tricks Tutorial in Oracle -2&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/embed\/#?secret=25qZreekNw#?secret=a4VN4FDxO7\" data-secret=\"a4VN4FDxO7\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #ff0000;\">Bulk Insert in Oracle<\/span><\/h3>\n<p>You should use Bulk Insert,Delete and Update instead of Single Insert,Delete and Update if you are executing Bulk operation.<\/p>\n<p>Bulk insert,Delete and Update will give your system a huge performance boost.<\/p>\n<p>&nbsp;<\/p>\n<p><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter size-full wp-image-11613\" src=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/bulk-load-performance.jpg\" alt=\"\" width=\"482\" height=\"326\" srcset=\"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/bulk-load-performance.jpg 482w, https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/bulk-load-performance-300x203.jpg 300w\" sizes=\"auto, (max-width: 482px) 100vw, 482px\" \/><\/p>\n<p>&nbsp;<\/p>\n<p>If you insert high volume data serial,\u00a0 you should switch this insert operation to the Bulk Insert.<\/p>\n<p>For example; you can insert all MSD.TEST_TABLE records to the MSD.TEST_TABLE_NEW serial as follows.<\/p>\n<pre>insert into MSD.TEST_TABLE_NEW select * from MSD.TEST_TABLE;\r\ncommit;<\/pre>\n<p>&nbsp;<\/p>\n<p>But if MSD.TEST_TABLE count is very big, then you should use Cursor and Forall to perform Bulk Insert as follows.<\/p>\n<p>&nbsp;<\/p>\n<pre>DECLARE\r\nCURSOR c_liste\r\nIS\r\nselect * from MSD.TEST_TABLE;\r\n\r\nTYPE TListe IS TABLE OF MSD.TEST_TABLE%ROWTYPE;\r\n\r\nr_liste TListe;\r\nl_hata VARCHAR2 (20000);\r\nBEGIN\r\nOPEN c_liste;\r\n\r\nLOOP\r\nFETCH c_liste\r\nBULK COLLECT INTO r_liste\r\nLIMIT 1000;\r\n\r\nEXIT WHEN r_liste.COUNT () = 0;\r\n\r\nBEGIN\r\nFORALL i IN 1 .. r_liste.LAST\r\ninsert into MSD.TEST_TABLE_NEW\r\nVALUES r_liste (i);\r\nEND;\r\n\r\nCOMMIT;\r\nEND LOOP;\r\n\r\nCOMMIT;\r\n\r\nCLOSE c_liste;\r\nEND;\r\n\/<\/pre>\n<p>&nbsp;<\/p>\n<p>This Bulk Insert operation speed up to 30 times faster according to Serial Insert.<\/p>\n<p>&nbsp;<\/p>\n\n<p>&nbsp;<\/p>\n<h3><span style=\"color: #ff0000;\">Bulk Update in Oracle<\/span><\/h3>\n<p>&nbsp;<\/p>\n<p>Likewise you can perform bulk update as follows.<\/p>\n<pre>SET TIMING ON\r\n\r\nDECLARE\r\nCURSOR c_liste\r\nIS\r\nSELECT \/*+ PARALLEL(TT, 8) *\/ * FROM MSD.TEST_TABLE TT\r\nWHERE TT.SUBSCRIPTION_ID IS NOT NULL;\r\n\r\nTYPE TListe\r\nIS TABLE OF MSD.TEST_TABLE%ROWTYPE;\r\n\r\nr_liste TListe;\r\nl_hata VARCHAR2 (2000);\r\nBEGIN\r\nOPEN c_liste;\r\n\r\nLOOP\r\nFETCH c_liste\r\nBULK COLLECT INTO r_liste\r\nLIMIT 1000;\r\n\r\nEXIT WHEN r_liste.COUNT () = 0;\r\n\r\nBEGIN\r\nFORALL i IN 1 .. r_liste.LAST\r\nUPDATE PR_OSB_CUSTOM.SEQUENCING_RUNTIME_MAIN SEQ\r\nSET SEQ.MESSAGE = updateXML(SEQ.MESSAGE, '\/\/*:businessID\/text()', 'MSD-'||r_liste (i).SUBSCRIPTION_ID|| '_' || r_liste (i).SYSTEM_NAME),\r\nSEQ.BUSINESS_ID = 'MSD-'||r_liste (i).SUBSCRIPTION_ID|| '_' || r_liste (i).SYSTEM_NAME\r\nWHERE SEQ.ID = r_liste (i).ID\r\nAND seq.BUSINESS_ID NOT LIKE 'MSD%';\r\n\r\nEND;\r\n\r\nCOMMIT;\r\nEND LOOP;\r\n\r\nCOMMIT;\r\n\r\nCLOSE c_liste;\r\nEND;\r\n\/<\/pre>\n<p>&nbsp;<\/p>\n<p>If you are processing ( DML ) high volume data, you should use Bulk Insert or Bulk Update or Bulk Delete instead of Serial Insert, update and delete.<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<p>&nbsp;<\/p>\n<h4><a href=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/\" target=\"_blank\" rel=\"noopener noreferrer\"><span style=\"color: #ff0000;\">\u00a0Do you want to learn Top 30 SQL Tuning Tips and Tricks, then Click this link adn read the articles.<\/span><\/a><\/h4>\n<blockquote class=\"wp-embedded-content\" data-secret=\"a4VN4FDxO7\"><p><a href=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/\">SQL Tuning Tips and Tricks Tutorial in Oracle -2<\/a><\/p><\/blockquote>\n<p><iframe loading=\"lazy\" class=\"wp-embedded-content\" sandbox=\"allow-scripts\" security=\"restricted\" style=\"position: absolute; clip: rect(1px, 1px, 1px, 1px);\" title=\"&#8220;SQL Tuning Tips and Tricks Tutorial in Oracle -2&#8221; &#8212; IT Tutorial\" src=\"https:\/\/ittutorial.org\/sql-tuning-tips-and-tricks-tutorial-oracle-2\/embed\/#?secret=25qZreekNw#?secret=a4VN4FDxO7\" data-secret=\"a4VN4FDxO7\" width=\"600\" height=\"338\" frameborder=\"0\" marginwidth=\"0\" marginheight=\"0\" scrolling=\"no\"><\/iframe><\/p>\n<p>&nbsp;<\/p>\n","protected":false},"excerpt":{"rendered":"<p>I will explain Bulk Insert and Bulk Update in Oracle Database in this article.<\/p>\n","protected":false},"author":1,"featured_media":11613,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"om_disable_all_campaigns":false,"_monsterinsights_skip_tracking":false,"_monsterinsights_sitenote_active":false,"_monsterinsights_sitenote_note":"","_monsterinsights_sitenote_category":0,"_uf_show_specific_survey":0,"_uf_disable_surveys":false,"_jetpack_memberships_contains_paid_content":false,"footnotes":""},"categories":[1994,3019,6603],"tags":[7617,7609,7610,7619,7620,15990,7616,7605,7626,19591,19592,19593,19594,7628,7629,19595,7621,7614,7604,7607,7612,7608,7606,7611,7615,7618,7625,7623,7624,7613],"class_list":["post-13983","post","type-post","status-publish","format-standard","has-post-thumbnail","","category-oracle","category-oracle-sql","category-sql-tuning","tag-bulk-collect-in-oracle","tag-bulk-collect-in-oracle-example","tag-bulk-collect-in-oracle-with-limit-clause-example","tag-bulk-collect-multiple-columns-in-oracle","tag-bulk-exceptions-in-oracle","tag-bulk-insert-and-bulk-update-in-oracle","tag-bulk-insert-in-oracle","tag-bulk-insert-in-oracle-stored-procedure","tag-bulk-insert-in-sql-from-excel-file","tag-bulk-insert-oracle","tag-bulk-insert-oracle-java","tag-bulk-insert-oracle-jdbc","tag-bulk-insert-oracle-python","tag-bulk-insert-sql","tag-bulk-insert-sql-specify-columns","tag-bulk-update-oracle","tag-delete-parallel-oracle","tag-how-to-make-update-faster-in-oracle","tag-insert-into-select-vs-bulk-insert-oracle","tag-oracle-bulk-collect-forall-insert-example","tag-oracle-bulk-collect-insert","tag-oracle-bulk-insert","tag-oracle-bulk-insert-from-file","tag-oracle-bulk-insert-from-one-table-to-another","tag-oracle-bulk-update-from-another-table","tag-oracle-delete-1000-rows-at-a-time","tag-parallel-bulk-insert-sql","tag-sql-bulk-insert","tag-stored-procedure-for-bulk-insert-sql","tag-update-huge-number-records-oracle"],"aioseo_notices":[],"jetpack_featured_media_url":"https:\/\/ittutorial.org\/wp-content\/uploads\/2019\/10\/bulk-load-performance.jpg","jetpack_sharing_enabled":true,"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/13983","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/comments?post=13983"}],"version-history":[{"count":4,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/13983\/revisions"}],"predecessor-version":[{"id":34703,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/posts\/13983\/revisions\/34703"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media\/11613"}],"wp:attachment":[{"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/media?parent=13983"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/categories?post=13983"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/ittutorial.org\/wp-json\/wp\/v2\/tags?post=13983"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}