{"id":11886,"date":"2022-10-02T20:24:39","date_gmt":"2022-10-02T11:24:39","guid":{"rendered":"https:\/\/prodskill.com\/?p=11886"},"modified":"2022-10-04T23:56:46","modified_gmt":"2022-10-04T14:56:46","slug":"oracle-dbms-parallel-execute-4-chunk-by-sql","status":"publish","type":"post","link":"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/","title":{"rendered":"4. User-defined SQL partitioning method parallel processing case (DBMS_PARALLEL_EXECUTE)"},"content":{"rendered":"<p>Let&#039;s take a look at the case of user-defined SQL partitioning parallelism using Oracle DBMS_PARALLEL_EXECUTE. It covers writing user-defined SQL, test environment, job creation, job division, job execution, job completion confirmation and deletion.<\/p>\n\n\n\n<p>This is a continuation of the previous article.<\/p>\n\n\n\n<p><a href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-3-chunk-by-number-column\/\">3. NUMBER Column Split Method Parallel Processing Case (DBMS_PARALLEL_EXECUTE)<\/a><\/p>\n\n\n\n<p>* Reference Oracle documentation: <a href=\"https:\/\/docs.oracle.com\/cd\/E11882_01\/appdev.112\/e40758\/d_parallel_ex.htm#ARPLS67359\" target=\"_blank\" rel=\"noreferrer noopener\">DBMS_PARALLEL_EXECUTE \u2013 CREATE_CHUNKS_BY_SQL Procedure (oracle.com)<\/a><\/p>\n\n\n\n<div id=\"ez-toc-container\" class=\"ez-toc-v2_0_82_2 counter-hierarchy ez-toc-counter ez-toc-grey ez-toc-container-direction\">\n<div class=\"ez-toc-title-container\">\n<p class=\"ez-toc-title\" style=\"cursor:inherit\">&lt;&lt;Table of Contents&gt;&gt;<\/p>\n<span class=\"ez-toc-title-toggle\"><a href=\"#\" class=\"ez-toc-pull-right ez-toc-btn ez-toc-btn-xs ez-toc-btn-default ez-toc-toggle\" aria-label=\"Toggle Table of Content\"><span class=\"ez-toc-js-icon-con\"><span class=\"\"><span class=\"eztoc-hide\" style=\"display:none;\">Toggle<\/span><span class=\"ez-toc-icon-toggle-span\"><svg style=\"fill: #999;color:#999\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" class=\"list-377408\" width=\"20px\" height=\"20px\" viewbox=\"0 0 24 24\" fill=\"none\"><path d=\"M6 6H4v2h2V6zm14 0H8v2h12V6zM4 11h2v2H4v-2zm16 0H8v2h12v-2zM4 16h2v2H4v-2zm16 0H8v2h12v-2z\" fill=\"currentColor\"><\/path><\/svg><svg style=\"fill: #999;color:#999\" class=\"arrow-unsorted-368013\" xmlns=\"http:\/\/www.w3.org\/2000\/svg\" width=\"10px\" height=\"10px\" viewbox=\"0 0 24 24\" version=\"1.2\" baseprofile=\"tiny\"><path d=\"M18.2 9.3l-6.2-6.3-6.2 6.3c-.2.2-.3.4-.3.7s.1.5.3.7c.2.2.4.3.7.3h11c.3 0 .5-.1.7-.3.2-.2.3-.5.3-.7s-.1-.5-.3-.7zM5.8 14.7l6.2 6.3 6.2-6.3c.2-.2.3-.5.3-.7s-.1-.5-.3-.7c-.2-.2-.4-.3-.7-.3h-11c-.3 0-.5.1-.7.3-.2.2-.3.5-.3.7s.1.5.3.7z\"\/><\/svg><\/span><\/span><\/span><\/a><\/span><\/div>\n<nav><ul class='ez-toc-list ez-toc-list-level-1' ><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-1\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#4_%EC%82%AC%EC%9A%A9%EC%9E%90_%EC%A0%95%EC%9D%98_SQL_%EB%B6%84%ED%95%A0_%EB%B0%A9%EC%8B%9D_%EB%B3%91%EB%A0%AC_%EC%B2%98%EB%A6%AC_%EC%82%AC%EB%A1%80\" >4. User Defined SQL Partitioning Parallel Processing Case<\/a><ul class='ez-toc-list-level-3' ><li class='ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-2\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#41_%EC%82%AC%EC%9A%A9%EC%9E%90_%EC%A0%95%EC%9D%98_SQL_%EB%B6%84%ED%95%A0_%EB%B0%A9%EC%8B%9D_%EA%B0%9C%EC%9A%94\" >4.1. Overview of custom SQL partitioning methods<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-3\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#42_%EC%82%AC%EC%9A%A9%EC%9E%90_%EC%A0%95%EC%9D%98_SQL_%EC%9E%91%EC%84%B1\" >4.2. Writing custom SQL<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-4\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#43_%ED%85%8C%EC%8A%A4%ED%8A%B8_%ED%99%98%EA%B2%BD_%EB%B0%8F_%ED%85%8C%EC%8A%A4%ED%8A%B8_%ED%85%8C%EC%9D%B4%EB%B8%94_%EC%83%9D%EC%84%B1\" >4.3. Create test environment and test table<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-5\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#44_%EC%9E%91%EC%97%85_%EC%83%9D%EC%84%B1\" >4.4. create job<\/a><ul class='ez-toc-list-level-4' ><li class='ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-6\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#441_%EC%9E%91%EC%97%85%EC%83%9D%EC%84%B1\" >4.4.1. create job<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-4'><a class=\"ez-toc-link ez-toc-heading-7\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#442_%EC%9E%91%EC%97%85_%EB%8B%A8%EC%9C%84_%EB%B6%84%ED%95%A0\" >4.4.2. Split work unit<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-8\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#45_%EC%9E%91%EC%97%85_%EC%8B%A4%ED%96%89\" >4.5. job run<\/a><\/li><li class='ez-toc-page-1 ez-toc-heading-level-3'><a class=\"ez-toc-link ez-toc-heading-9\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#46_%EC%9E%91%EC%97%85_%EC%99%84%EB%A3%8C_%ED%99%95%EC%9D%B8_%EB%B0%8F_%EC%82%AD%EC%A0%9C\" >4.6. Confirm task completion and delete<\/a><\/li><\/ul><\/li><li class='ez-toc-page-1 ez-toc-heading-level-2'><a class=\"ez-toc-link ez-toc-heading-10\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#5_%EA%B3%A0%EB%A0%A4%EC%82%AC%ED%95%AD\" >5. Considerations<\/a><\/li><\/ul><\/nav><\/div>\n<h2 class=\"wp-block-heading\" id=\"4._\uc0ac\uc6a9\uc790_\uc815\uc758_SQL_\ubd84\ud560_\ubc29\uc2dd\uc758_\ubcd1\ub82c_\ucc98\ub9ac_\uc0ac\ub840\"><span class=\"ez-toc-section\" id=\"4_%EC%82%AC%EC%9A%A9%EC%9E%90_%EC%A0%95%EC%9D%98_SQL_%EB%B6%84%ED%95%A0_%EB%B0%A9%EC%8B%9D_%EB%B3%91%EB%A0%AC_%EC%B2%98%EB%A6%AC_%EC%82%AC%EB%A1%80\"><\/span>4. User Defined SQL Partitioning Parallel Processing Case<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"4.1._\uc0ac\uc6a9\uc790_\uc815\uc758_SQL_\ubd84\ud560_\ubc29\uc2dd_\uac1c\uc694\"><span class=\"ez-toc-section\" id=\"41_%EC%82%AC%EC%9A%A9%EC%9E%90_%EC%A0%95%EC%9D%98_SQL_%EB%B6%84%ED%95%A0_%EB%B0%A9%EC%8B%9D_%EA%B0%9C%EC%9A%94\"><\/span>4.1. Overview of custom SQL partitioning methods<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Partitioning through user-defined SQL is useful in the following cases.<\/p>\n\n\n\n<ul class=\"wp-block-list\"><li>Partitioning in cases where the ROWID partitioning method does not support (e.g. ROWID partitioning for remote tables via DB Link)<\/li><li>Split based on columns other than NUMBER column (VARCHAR2, DATE, etc.)<\/li><\/ul>\n\n\n\n<p>Here, we will explain the case of ROWID division through DB Link in the first case.<\/p>\n\n\n\n<p>If you try to split the ROWID of a table through DB Link using CREATE_CHUNKS_BY_ROWID , An error occurs.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- 1\ub2e8\uacc4: \uc791\uc5c5\uc0dd\uc131\nBEGIN\n  DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'DPE_TEST(BY ROWID, VIA DBLINK)');\nEND;\n\/\n\n-- 2\ub2e8\uacc4: \uc791\uc5c5 \ub2e8\uc704 \ubd84\ud560\nBEGIN\n  DBMS_PARALLEL_EXECUTE\n    .CREATE_CHUNKS_BY_ROWID(TASK_NAME   => 'DPE_TEST(BY ROWID, VIA DBLINK)',\n                            TABLE_OWNER => USER,\n                            -- TABLE _NAME\uc744 \u201cT1@DL_MS949\u201d\ub85c DB Link \uc9c0\uc815\n                            TABLE_NAME  => 'T1@DL_MS949',\n                            BY_ROW      => TRUE,\n                            CHUNK_SIZE  => 10000);\nEND;\n\/\n\n--> \uc2e4\ud589 \uc624\ub958 \uba54\uc2dc\uc9c0\nORA-29491: \uc870\uac01\uc5d0 \ubd80\uc801\ud569\ud55c \ud14c\uc774\ube14\nORA-06512: \"SYS.DBMS_PARALLEL_EXECUTE\",  27\ud589\nORA-06512: \"SYS.DBMS_PARALLEL_EXECUTE\",  121\ud589\nORA-06512:  4\ud589<\/pre>\n\n\n\n<p>In this case, SQL that divides the ROWID of the table on the DB Link can be created and applied through CREATE_CHUNKS_BY_SQL.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL (\n   task_name  IN  VARCHAR2,\n   sql_stmt   IN  CLOB,\n   by_rowid   IN  BOOLEAN);<\/pre>\n\n\n\n<p>sql_stmt is a CLOB type and can be used with almost no restrictions on its length, but here we suggest a way to use a pipe-lined function rather than directly describe SQL.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"4.2._\uc0ac\uc6a9\uc790_\uc815\uc758_SQL_\uc791\uc131\"><span class=\"ez-toc-section\" id=\"42_%EC%82%AC%EC%9A%A9%EC%9E%90_%EC%A0%95%EC%9D%98_SQL_%EC%9E%91%EC%84%B1\"><\/span>4.2. Writing custom SQL<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Create a user-defined type and create a pipe-lined function that returns a result set of this type as follows.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- 1. TYPE \uc0dd\uc131 (Pipe-Lined function\uc5d0\uc11c return\ud558\uae30 \uc704\ud568)\nCREATE OR REPLACE TYPE TP_ROWID_RANGE AS OBJECT (\n    START_ROWID VARCHAR2(50)\n   ,END_ROWID   VARCHAR2(50)\n);\n\nCREATE OR REPLACE TYPE TL_ROWID_RANGE AS TABLE OF TP_ROWID_RANGE;\n\n-- 2. Function \uc0dd\uc131\nCREATE OR REPLACE FUNCTION FN_SPLIT_BY_ROWID(\n    I_OWNER IN VARCHAR2, I_TABLE_NAME IN VARCHAR2, I_CHUNKS IN NUMBER)\nRETURN TL_ROWID_RANGE\nPIPELINED\nAS\n  CURSOR C_ROWID_RANGE (CP_OWNER VARCHAR2, CP_TABLE_NAME VARCHAR2, CP_CHUNKS NUMBER)\n  IS\n    SELECT GRP,\n           DBMS_ROWID.ROWID_CREATE( 1, DATA_OBJECT_ID, LO_FNO, LO_BLOCK, 0 ) MIN_RID,\n           DBMS_ROWID.ROWID_CREATE( 1, DATA_OBJECT_ID, HI_FNO, HI_BLOCK, 10000 ) MAX_RID\n      FROM (\n            SELECT DISTINCT GRP,\n                   FIRST_VALUE(RELATIVE_FNO) \n                    OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID\n                          ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LO_FNO,\n                   FIRST_VALUE(BLOCK_ID) \n                   OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID\n                        ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) LO_BLOCK,\n                   LAST_VALUE(RELATIVE_FNO) \n                    OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID\n                    ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HI_FNO,\n                   LAST_VALUE(BLOCK_ID+BLOCKS-1) \n                    OVER (PARTITION BY GRP ORDER BY RELATIVE_FNO, BLOCK_ID\n                     ROWS BETWEEN UNBOUNDED PRECEDING AND UNBOUNDED FOLLOWING) HI_BLOCK,\n                   SUM(BLOCKS) OVER (PARTITION BY GRP) SUM_BLOCKS\n              FROM (\n                    SELECT RELATIVE_FNO, BLOCK_ID, BLOCKS,\n                           TRUNC( (SUM(BLOCKS) OVER (ORDER BY RELATIVE_FNO, BLOCK_ID)-0.01) \/\n                                  (SUM(BLOCKS) OVER ()\/ CP_CHUNKS) ) GRP\n                      FROM DBA_EXTENTS@DL_MS949\n                     WHERE SEGMENT_NAME = UPPER(CP_TABLE_NAME)\n                       AND OWNER = UPPER(CP_OWNER)\n                     ORDER BY BLOCK_ID\n                   )\n           ),\n           (SELECT DATA_OBJECT_ID\n              FROM DBA_OBJECTS@DL_MS949\n             WHERE OWNER = UPPER(CP_OWNER)\n               AND OBJECT_NAME = UPPER(CP_TABLE_NAME))\n     ORDER BY GRP\n    ;\nBEGIN\n  FOR ROWID_RANGE IN C_ROWID_RANGE(I_OWNER, I_TABLE_NAME, I_CHUNKS) LOOP\n      PIPE ROW(TP_ROWID_RANGE(ROWID_RANGE.MIN_RID, ROWID_RANGE.MAX_RID));\n  END LOOP;\n  RETURN;\nEND;\n\/<\/pre>\n\n\n\n<p>The SQL used in the above function is ROWID division in block units based on DBA_EXTENTS, and was slightly modified to use DB Link by referring to the technique suggested by Thomas Kyte.<\/p>\n\n\n\n<p style=\"font-size:16px\">* Reference URL:&nbsp;<a href=\"https:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:0::::P11_QUESTION_ID:10498431232211\" target=\"_blank\" rel=\"noreferrer noopener\">https:\/\/asktom.oracle.com\/pls\/asktom\/f?p=100:11:0::::P11_QUESTION_ID:10498431232211<\/a><\/p>\n\n\n\n<p>DB Link was used by specifying DL_MS949. If you want to dynamically designate DB Link as well, you can change the cursor SQL of the above function to dynamic SQL and use it.<\/p>\n\n\n\n<p>If the LEG.SUB_MON_STAT table (total number of 7,426) is partitioned by ROWID using this function, it is as follows.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- DL_MS949 DB Link\uc0c1\uc758 LEG owner, SUB_MON_STAT table\uc5d0 \ub300\ud574 4\uac1c\uc758 Chunk\ub85c ROWID \ubd84\ud560\nSELECT ROWNUM RNO, START_ROWID, END_ROWID\n  FROM TABLE(FN_SPLIT_BY_ROWID('LEG', 'SUB_MON_STAT', 4))\n;<\/pre>\n\n\n\n<figure class=\"wp-block-table\"><table><tbody><tr><td><strong>Row#<\/strong><\/td><td><strong>START_ROWID<\/strong><\/td><td><strong>END_ROWID<\/strong><\/td><\/tr><tr><td>1<\/td><td>AAAQXFAAEAAAACIAAA<\/td><td>AAAQXFAAEAAAAC3CcQ<\/td><\/tr><tr><td>2<\/td><td>AAAQXFAAEAAAAC4AAA<\/td><td>AAAQXFAAAEAAAADHCcQ<\/td><\/tr><tr><td>3<\/td><td>AAAQXFAAAEAAAADIAAA<\/td><td>AAAQXFAAAEAAAADXCcQ<\/td><\/tr><tr><td>4<\/td><td>AAAQXFAAEAAAADYAAA<\/td><td>AAAQXFAAAEAAAADnCcQ<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>When splitting data with the ROWID generated here, check whether there are any omissions in the entire data with the SQL below.&nbsp;<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">SELECT R.RNO, COUNT(*) CNT\n  FROM SUB_MON_STAT S\n      ,(\n        SELECT 1 RNO, 'AAAQXFAAEAAAACIAAA' START_ROWID, 'AAAQXFAAEAAAAC3CcQ' END_ROWID FROM DUAL\n        UNION ALL\n        SELECT 2 RNO, 'AAAQXFAAEAAAAC4AAA' START_ROWID, 'AAAQXFAAEAAAADHCcQ' END_ROWID FROM DUAL\n        UNION ALL\n        SELECT 3 RNO, 'AAAQXFAAEAAAADIAAA' START_ROWID, 'AAAQXFAAEAAAADXCcQ' END_ROWID FROM DUAL\n        UNION ALL\n        SELECT 4 RNO, 'AAAQXFAAEAAAADYAAA' START_ROWID, 'AAAQXFAAEAAAADnCcQ' END_ROWID FROM DUAL\n       ) R\n WHERE S.ROWID BETWEEN R.START_ROWID AND END_ROWID\n GROUP BY R.RNO\n ORDER BY R.RNO\n;<\/pre>\n\n\n\n<p>The execution result is as follows. (CNT may vary for each test environment.)<\/p>\n\n\n\n<figure class=\"wp-block-table\"><table class=\"has-fixed-layout\"><tbody><tr><td>RNO (chunk no)<\/td><td>CNT<\/td><\/tr><tr><td>1<\/td><td>1,790<\/td><\/tr><tr><td>2<\/td><td>2,206<\/td><\/tr><tr><td>3<\/td><td>2,209<\/td><\/tr><tr><td>4<\/td><td>1,221<\/td><\/tr><\/tbody><\/table><\/figure>\n\n\n\n<p>The sum of CNT is 7,426, which is equal to the total number of rows in the table, confirming that there is no omission. Here, the number of rows divided into each RNO is 1790, 2206, 2209, and 1221, respectively, which are not equal. This is the same as the reason for not being equally partitioned as described in the ROWID partitioning method.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"4.3._\ud14c\uc2a4\ud2b8_\ud658\uacbd_\ubc0f_\ud14c\uc2a4\ud2b8_\ud14c\uc774\ube14_\uc0dd\uc131\"><span class=\"ez-toc-section\" id=\"43_%ED%85%8C%EC%8A%A4%ED%8A%B8_%ED%99%98%EA%B2%BD_%EB%B0%8F_%ED%85%8C%EC%8A%A4%ED%8A%B8_%ED%85%8C%EC%9D%B4%EB%B8%94_%EC%83%9D%EC%84%B1\"><\/span>4.3. Create test environment and test table<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Target DB at<dl> Source DB using DB Link of<sub> table<sub> Assuming a test scenario imported into , proceed to the following environment configuration.<\/p>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png\"><img loading=\"lazy\" decoding=\"async\" width=\"390\" height=\"207\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png\" alt=\"\uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \ud14c\uc2a4\ud2b8 \ud658\uacbd \uac1c\ub150\ub3c4\" class=\"wp-image-11887\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png 390w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64-300x159.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64-18x10.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64-24x13.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64-36x19.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64-48x25.png 48w\" sizes=\"auto, (max-width: 390px) 100vw, 390px\" \/><\/a><figcaption>Conceptual diagram of user-defined SQL split-type parallel processing test environment<\/figcaption><\/figure>\n<\/div>\n\n\n<p>Target DB<sub> The table is created in advance with the following DDL.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">CREATE TABLE SUB_MON_STAT_COPY\nAS\nSELECT USE_MON, LINE_NUM, SUB_STA_ID, SUB_STA_NM, RIDE_PASGR_NUM, ALIGHT_PASGR_NUM, WORK_DT\n   FROM SUB_MON_STAT@DL_MS949\n WHERE 1=2;<\/pre>\n\n\n\n<h3 class=\"wp-block-heading\" id=\"4.4._\uc791\uc5c5_\uc0dd\uc131\"><span class=\"ez-toc-section\" id=\"44_%EC%9E%91%EC%97%85_%EC%83%9D%EC%84%B1\"><\/span>4.4. create job<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"441_%EC%9E%91%EC%97%85%EC%83%9D%EC%84%B1\"><\/span>4.4.1. create job<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- 1\ub2e8\uacc4: \uc791\uc5c5\uc0dd\uc131\nBEGIN\n  DBMS_PARALLEL_EXECUTE.CREATE_TASK(TASK_NAME => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)');\nEND;\n\/\n\n-- \uc791\uc5c5 \uc0dd\uc131 \ud655\uc778\nSELECT * FROM USER_PARALLEL_EXECUTE_TASKS;<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"536\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-1024x536.png\" alt=\"1\ub2e8\uacc4: \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc791\uc5c5 \uc0dd\uc131 \ud655\uc778\" class=\"wp-image-11888\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-1024x536.png 1024w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-300x157.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-768x402.png 768w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-18x9.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-24x13.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-36x19.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65-48x25.png 48w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-65.png 1028w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Step 1: Verify Create Custom SQL Partitioned Parallel Processing Job<\/figcaption><\/figure>\n<\/div>\n\n\n<h4 class=\"wp-block-heading\"><span class=\"ez-toc-section\" id=\"442_%EC%9E%91%EC%97%85_%EB%8B%A8%EC%9C%84_%EB%B6%84%ED%95%A0\"><\/span>4.4.2. Split work unit<span class=\"ez-toc-section-end\"><\/span><\/h4>\n\n\n\n<p>Using the FN_SPLIT_BY_ROWID function, designate\/split the work unit into 4.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- 2\ub2e8\uacc4: \uc791\uc5c5 \ub2e8\uc704 \ubd84\ud560\nBEGIN\n  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(\n          TASK_NAME => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)',\n          SQL_STMT  => 'SELECT START_ROWID, END_ROWID FROM TABLE(FN_SPLIT_BY_ROWID(''LEG'', ''SUB_MON_STAT'', 4))',\n          BY_ROWID  => TRUE);\nEND;\n\/\n\n-- \uc791\uc5c5 \ubd84\ud560 \uc0c1\ud0dc \ud655\uc778\nSELECT *\n  FROM USER_PARALLEL_EXECUTE_CHUNKS WHERE TASK_NAME = 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)';<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"536\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-1024x536.png\" alt=\"2\ub2e8\uacc4: \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc791\uc5c5 \ubd84\ud560 \uc0c1\ud0dc \ud655\uc778\" class=\"wp-image-11889\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-1024x536.png 1024w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-300x157.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-768x402.png 768w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-18x9.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-24x13.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-36x19.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66-48x25.png 48w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-66.png 1028w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Step 2: Check the user-defined SQL partitioning parallelism task splitting status<\/figcaption><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"4.5._\uc791\uc5c5_\uc2e4\ud589\"><span class=\"ez-toc-section\" id=\"45_%EC%9E%91%EC%97%85_%EC%8B%A4%ED%96%89\"><\/span>4.5. job run<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>Execute the task by specifying the ROWID condition in the WHERE clause. Here, the number of tasks is set to 4, the same as the number of work units.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- 3\ub2e8\uacc4: \uc791\uc5c5 \uc2e4\ud589\nDECLARE\n  L_SQL_STMT VARCHAR2(32767);\nBEGIN\n  L_SQL_STMT := 'INSERT INTO SUB_MON_STAT_COPY\n                 SELECT USE_MON, LINE_NUM, SUB_STA_ID, SUB_STA_NM,RIDE_PASGR_NUM, ALIGHT_PASGR_NUM, WORK_DT\n                   FROM SUB_MON_STAT@DL_MS949\n                  WHERE ROWID BETWEEN :START_ID AND :END_ID';\n\n  DBMS_PARALLEL_EXECUTE.RUN_TASK(TASK_NAME      => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)',\n                                 SQL_STMT       => L_SQL_STMT,\n                                 LANGUAGE_FLAG  => DBMS_SQL.NATIVE,\n                                 PARALLEL_LEVEL => 4);\nEND;\n\/\n\n-- \uc791\uc5c5 \uc2e4\ud589\uc0c1\ud669, \uc624\ub958\ucf54\ub4dc\/\uba54\uc2dc\uc9c0 \ud655\uc778\nSELECT  *\n  FROM  USER_PARALLEL_EXECUTE_CHUNKS\n WHERE  TASK_NAME = 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)';<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-large\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67.png\"><img loading=\"lazy\" decoding=\"async\" width=\"1024\" height=\"667\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-1024x667.png\" alt=\"3\ub2e8\uacc4: \uc791\uc5c5 \uc2e4\ud589 \uc0c1\ud669 \ud655\uc778\" class=\"wp-image-11890\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-1024x667.png 1024w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-300x196.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-768x501.png 768w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-18x12.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-24x16.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-36x23.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67-48x31.png 48w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-67.png 1028w\" sizes=\"auto, (max-width: 1024px) 100vw, 1024px\" \/><\/a><figcaption>Step 3: Check Execution Status<\/figcaption><\/figure>\n<\/div>\n\n\n<h3 class=\"wp-block-heading\" id=\"4.6._\uc791\uc5c5_\uc644\ub8cc_\ud655\uc778_\ubc0f_\uc0ad\uc81c\"><span class=\"ez-toc-section\" id=\"46_%EC%9E%91%EC%97%85_%EC%99%84%EB%A3%8C_%ED%99%95%EC%9D%B8_%EB%B0%8F_%EC%82%AD%EC%A0%9C\"><\/span>4.6. Confirm task completion and delete<span class=\"ez-toc-section-end\"><\/span><\/h3>\n\n\n\n<p>You can check the job completion with the following SQL.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- \uc791\uc5c5 \uc644\ub8cc \ud655\uc778\nSELECT *\n  FROM USER_PARALLEL_EXECUTE_TASKS;<\/pre>\n\n\n<div class=\"wp-block-image\">\n<figure class=\"aligncenter size-full\"><a href=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68.png\"><img loading=\"lazy\" decoding=\"async\" width=\"753\" height=\"491\" src=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68.png\" alt=\"\uc791\uc5c5 \uc644\ub8cc \ud655\uc778\" class=\"wp-image-11891\" srcset=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68.png 753w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68-300x196.png 300w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68-18x12.png 18w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68-24x16.png 24w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68-36x23.png 36w, https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-68-48x31.png 48w\" sizes=\"auto, (max-width: 753px) 100vw, 753px\" \/><\/a><figcaption>Confirm task completion<\/figcaption><\/figure>\n<\/div>\n\n\n<p>DROP_TASK( ) to delete the job.<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"sql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\" data-no-auto-translation=\"\">-- 4\ub2e8\uacc4: \uc791\uc5c5\uc0ad\uc81c\nBEGIN\n  DBMS_PARALLEL_EXECUTE.DROP_TASK(TASK_NAME => 'DPE_TEST(BY SQL(ROWID), VIA DBLINK)');\nEND;\n\/<\/pre>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"5._\uace0\ub824\uc0ac\ud56d\"><span class=\"ez-toc-section\" id=\"5_%EA%B3%A0%EB%A0%A4%EC%82%AC%ED%95%AD\"><\/span>5. Considerations<span class=\"ez-toc-section-end\"><\/span><\/h2>\n\n\n\n<ul class=\"wp-block-list\"><li>When the target table is a partitioned table and the DML is an INSERT<ul><li>When partitioning by the number of partitions and processing data in units of partition keys, Direct Path I\/O is expected to be possible. (I haven&#039;t tested it, but it seems possible)<\/li><li>Need to use \/*+ APPEND *\/ hint in INSERT syntax and set partition to NOLOGGING<\/li><\/ul><\/li><li>If the target table is a non-partitioned table<ul><li>Direct Path I\/O is not possible and only conventional I\/O is possible<\/li><li>Since the amount of UNDO can be quite large, it is necessary to secure free storage space in advance.<\/li><li>If you set the chunk size to a small size, you will be able to limit the size of UNDO to some extent.<\/li><\/ul><\/li><\/ul>\n\n\n\n<hr class=\"wp-block-separator has-alpha-channel-opacity is-style-dots\"\/>\n\n\n\n<p>Above, we looked at how to use DBMS_PARALLEL_EXECUTE. This is a method I found while searching for parallel loading of tables containing CLOB columns in a project a few years ago through DB Link. I hope I explained it well enough for anyone who wants to use it.&nbsp;<\/p>\n\n\n\n<p>If you have any questions, please leave them in the comments.<\/p>","protected":false},"excerpt":{"rendered":"<p>Let&#039;s take a look at the case of user-defined SQL partitioning parallelism using Oracle DBMS_PARALLEL_EXECUTE. It covers writing user-defined SQL, test environment, job creation, job division, job execution, job completion confirmation and deletion. This is a continuation of the previous article. 3. NUMBER...<\/p>","protected":false},"author":1,"featured_media":11887,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32],"tags":[242,243,244,245,246],"class_list":["post-11886","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-oracle","tag-oracle","tag-dbms_parallel_execute","tag-244","tag-dml-parallel-processing","tag-dml-"],"yoast_head":"<!-- This site is optimized with the Yoast SEO plugin v27.4 - https:\/\/yoast.com\/product\/yoast-seo-wordpress\/ -->\n<title>4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE) - \uc0dd\uc0b0\uc131 Skill<\/title>\n<meta name=\"description\" content=\"Oracle DBMS_PARALLEL_EXECUTE \ub97c \ud65c\uc6a9\ud558\uc5ec \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840\uc5d0 \ub300\ud574 \uc0b4\ud3b4\ubcf8\ub2e4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \uc791\uc131, \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14 \uc0dd\uc131, \uc791\uc5c5 \uc0dd\uc131, \uc791\uc5c5 \ub2e8\uc704 \ubd84\ud560, \uc791\uc5c5 \uc2e4\ud589, \uc791\uc5c5 \uc644\ub8cc \ud655\uc778 \ubc0f \uc0ad\uc81c\uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc774\ub2e4.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE) - \uc0dd\uc0b0\uc131 Skill\" \/>\n<meta property=\"og:description\" content=\"Oracle DBMS_PARALLEL_EXECUTE \ub97c \ud65c\uc6a9\ud558\uc5ec \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840\uc5d0 \ub300\ud574 \uc0b4\ud3b4\ubcf8\ub2e4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \uc791\uc131, \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14 \uc0dd\uc131, \uc791\uc5c5 \uc0dd\uc131, \uc791\uc5c5 \ub2e8\uc704 \ubd84\ud560, \uc791\uc5c5 \uc2e4\ud589, \uc791\uc5c5 \uc644\ub8cc \ud655\uc778 \ubc0f \uc0ad\uc81c\uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc774\ub2e4.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/\" \/>\n<meta property=\"og:site_name\" content=\"\uc0dd\uc0b0\uc131 Skill\" \/>\n<meta property=\"article:published_time\" content=\"2022-10-02T11:24:39+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-10-04T14:56:46+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png\" \/>\n\t<meta property=\"og:image:width\" content=\"390\" \/>\n\t<meta property=\"og:image:height\" content=\"207\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/png\" \/>\n<meta name=\"author\" content=\"Zerom\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Zerom\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"7 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\\\/\\\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#article\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/\"},\"author\":{\"name\":\"Zerom\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"headline\":\"4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE)\",\"datePublished\":\"2022-10-02T11:24:39+00:00\",\"dateModified\":\"2022-10-04T14:56:46+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/\"},\"wordCount\":379,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"image\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/image-64.png\",\"keywords\":[\"ORACLE\",\"DBMS_PARALLEL_EXECUTE\",\"\ubcd1\ub82c\ucc98\ub9ac\",\"DML Parallel Processing\",\"DML \ubcd1\ub82c\ucc98\ub9ac\"],\"articleSection\":[\"ORACLE\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/\",\"url\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/\",\"name\":\"4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE) - \uc0dd\uc0b0\uc131 Skill\",\"isPartOf\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#primaryimage\"},\"image\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#primaryimage\"},\"thumbnailUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/image-64.png\",\"datePublished\":\"2022-10-02T11:24:39+00:00\",\"dateModified\":\"2022-10-04T14:56:46+00:00\",\"description\":\"Oracle DBMS_PARALLEL_EXECUTE \ub97c \ud65c\uc6a9\ud558\uc5ec \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840\uc5d0 \ub300\ud574 \uc0b4\ud3b4\ubcf8\ub2e4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \uc791\uc131, \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14 \uc0dd\uc131, \uc791\uc5c5 \uc0dd\uc131, \uc791\uc5c5 \ub2e8\uc704 \ubd84\ud560, \uc791\uc5c5 \uc2e4\ud589, \uc791\uc5c5 \uc644\ub8cc \ud655\uc778 \ubc0f \uc0ad\uc81c\uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc774\ub2e4.\",\"breadcrumb\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#primaryimage\",\"url\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/image-64.png\",\"contentUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/10\\\/image-64.png\",\"width\":390,\"height\":207},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/oracle-dbms-parallel-execute-4-chunk-by-sql\\\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"Home\",\"item\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE)\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#website\",\"url\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/\",\"name\":\"\uc0dd\uc0b0\uc131 Skill\",\"description\":\"Meta Thinking, Meta Working\",\"publisher\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":[\"Person\",\"Organization\"],\"@id\":\"https:\\\/\\\/prodskill.com\\\/ko\\\/#\\\/schema\\\/person\\\/bbad0870c78008c82edbe0960fe768bd\",\"name\":\"Zerom\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"url\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"contentUrl\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\",\"width\":512,\"height\":512,\"caption\":\"Zerom\"},\"logo\":{\"@id\":\"https:\\\/\\\/prodskill.com\\\/wp-content\\\/uploads\\\/2022\\\/09\\\/productivity_clockgear.png\"},\"url\":\"https:\\\/\\\/prodskill.com\\\/en\\\/author\\\/proda\\\/\"}]}<\/script>\n<!-- \/ Yoast SEO plugin. -->","yoast_head_json":{"title":"4. User-Defined SQL Partitioning Parallel Processing Case (DBMS_PARALLEL_EXECUTE) - Productivity Skill","description":"This article explores a case study of user-defined SQL partitioning parallel processing using Oracle DBMS_PARALLEL_EXECUTE. It covers writing user-defined SQL, creating test tables, creating tasks, partitioning tasks, executing tasks, checking for task completion, and deleting tasks.","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:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/","og_locale":"en_US","og_type":"article","og_title":"4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE) - \uc0dd\uc0b0\uc131 Skill","og_description":"Oracle DBMS_PARALLEL_EXECUTE \ub97c \ud65c\uc6a9\ud558\uc5ec \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840\uc5d0 \ub300\ud574 \uc0b4\ud3b4\ubcf8\ub2e4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \uc791\uc131, \ud14c\uc2a4\ud2b8 \ud14c\uc774\ube14 \uc0dd\uc131, \uc791\uc5c5 \uc0dd\uc131, \uc791\uc5c5 \ub2e8\uc704 \ubd84\ud560, \uc791\uc5c5 \uc2e4\ud589, \uc791\uc5c5 \uc644\ub8cc \ud655\uc778 \ubc0f \uc0ad\uc81c\uc5d0 \ub300\ud55c \ub0b4\uc6a9\uc774\ub2e4.","og_url":"https:\/\/prodskill.com\/en\/oracle-dbms-parallel-execute-4-chunk-by-sql\/","og_site_name":"\uc0dd\uc0b0\uc131 Skill","article_published_time":"2022-10-02T11:24:39+00:00","article_modified_time":"2022-10-04T14:56:46+00:00","og_image":[{"width":390,"height":207,"url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png","type":"image\/png"}],"author":"Zerom","twitter_card":"summary_large_image","twitter_misc":{"Written by":"Zerom","Est. reading time":"7 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#article","isPartOf":{"@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/"},"author":{"name":"Zerom","@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"headline":"4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE)","datePublished":"2022-10-02T11:24:39+00:00","dateModified":"2022-10-04T14:56:46+00:00","mainEntityOfPage":{"@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/"},"wordCount":379,"commentCount":0,"publisher":{"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"image":{"@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png","keywords":["ORACLE","DBMS_PARALLEL_EXECUTE","\ubcd1\ub82c\ucc98\ub9ac","DML Parallel Processing","DML \ubcd1\ub82c\ucc98\ub9ac"],"articleSection":["ORACLE"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/","url":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/","name":"4. User-Defined SQL Partitioning Parallel Processing Case (DBMS_PARALLEL_EXECUTE) - Productivity Skill","isPartOf":{"@id":"https:\/\/prodskill.com\/ko\/#website"},"primaryImageOfPage":{"@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#primaryimage"},"image":{"@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#primaryimage"},"thumbnailUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png","datePublished":"2022-10-02T11:24:39+00:00","dateModified":"2022-10-04T14:56:46+00:00","description":"This article explores a case study of user-defined SQL partitioning parallel processing using Oracle DBMS_PARALLEL_EXECUTE. It covers writing user-defined SQL, creating test tables, creating tasks, partitioning tasks, executing tasks, checking for task completion, and deleting tasks.","breadcrumb":{"@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#primaryimage","url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png","contentUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/10\/image-64.png","width":390,"height":207},{"@type":"BreadcrumbList","@id":"https:\/\/prodskill.com\/oracle-dbms-parallel-execute-4-chunk-by-sql\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"Home","item":"https:\/\/prodskill.com\/ko\/"},{"@type":"ListItem","position":2,"name":"4. \uc0ac\uc6a9\uc790 \uc815\uc758 SQL \ubd84\ud560 \ubc29\uc2dd \ubcd1\ub82c \ucc98\ub9ac \uc0ac\ub840 (DBMS_PARALLEL_EXECUTE)"}]},{"@type":"WebSite","@id":"https:\/\/prodskill.com\/ko\/#website","url":"https:\/\/prodskill.com\/ko\/","name":"Productivity Skills","description":"Meta Thinking, Meta Working","publisher":{"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/prodskill.com\/ko\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":["Person","Organization"],"@id":"https:\/\/prodskill.com\/ko\/#\/schema\/person\/bbad0870c78008c82edbe0960fe768bd","name":"Zerom","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","url":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","contentUrl":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png","width":512,"height":512,"caption":"Zerom"},"logo":{"@id":"https:\/\/prodskill.com\/wp-content\/uploads\/2022\/09\/productivity_clockgear.png"},"url":"https:\/\/prodskill.com\/en\/author\/proda\/"}]}},"_links":{"self":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts\/11886","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/comments?post=11886"}],"version-history":[{"count":0,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/posts\/11886\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/media\/11887"}],"wp:attachment":[{"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/media?parent=11886"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/categories?post=11886"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/prodskill.com\/en\/wp-json\/wp\/v2\/tags?post=11886"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}