DBMS_PARALLEL_EXECUTE insert multiple views in same table parallel

Question

i want to insert the data from 7 views (defintely multiple views, less or more, depends...) into one table with DBMS_PARALLEL_EXECUTE, table structure and view structures are the same.

i am quite new how to handle this package and always facing the same error: variable not in select list, i tried different solutions and ending up with creating a wrapping procuedure dynamically but i do not getting my code to run:

  PROCEDURE load_data_as_task (
    strLoadTable varchar2,
    arrLoadViews arrv
    )
  AS
    strLoadTask       varchar2(80);
    strLoadView       varchar2(80);
    
    stmtWrapperProc   clob;
  BEGIN
  
    for lv in arrLoadViews.first..arrLoadViews.last loop
      
      -- set task name
      strLoadTask := arrLoadViews(lv)||'_task';

      -- creating wrapper procedure
      stmtWrapperProc := q'[
        CREATE OR REPLACE PROCEDURE insert_from_view(p_id number) AS
        BEGIN
          EXECUTE IMMEDIATE 'INSERT INTO ]'||strLoadTable||q'[ SELECT * FROM ]'||arrLoadViews(lv)||q'[';
        END;
        ]';
      execute immediate stmtWrapperProc;
      
      -- create Task
      DBMS_PARALLEL_EXECUTE.CREATE_TASK(strLoadTask);
      
      -- create one chunk per load view
      DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(
        task_name => strLoadTask, 
        sql_stmt => 'SELECT ' || to_char(lv) || ' AS view_id FROM dual',
        by_rowid => false
        );

      DBMS_PARALLEL_EXECUTE.RUN_TASK(
        task_name => strLoadTask,
        sql_stmt => 'BEGIN insert_from_view(:start_id); END;',
        --sql_stmt => 'DECLARE dummy NUMBER; BEGIN dummy := :start_id; EXECUTE IMMEDIATE ''INSERT INTO '||strLoadTable||' SELECT * FROM '||arrLoadViews(lv)||'''; END;',
        language_flag => DBMS_SQL.NATIVE,
        parallel_level => 1
        );
      
    end loop;
  END;

the procedure is insinde a package, load views and target table already created.

it would be great if anyone can help me to achieve this or maybe know a simpler way to load data from different views in one table parallel (at the same time)

thanks in advance!

Why not simply use a pdml-enabled INSERT SELECT statement with a set of UNION ALLs to concat the views? Whenever possible, use native query parallelism rather than more complicated techniques. — Paul W
– Paul W, Commented May 13 at 16:52
yeah, this answer is a perfect example why stackoverlfow is loosing attention i the light of AI... simple answer, a parallel query is not the same like DBMS_PARALLEL_EXECUTE in fact it is a complete different method, see: stackoverflow.com/questions/45221302/… doesn't matter, figured it out myself — Moribundus
– Moribundus, Commented May 25 at 13:12
correct, pdml is quite different than an awkward PL/SQL-based mechanism for pseuo-parallelization. It'll accomplish the same goal but in a manner that is inherently superior in just about every respect. And, most AI blurbs that google pops up with are incorrect - just close enough to be misleading to folks who aren't careful. We're a long way from AI that can be trusted over humans with decades of real-world experience. That's why SO will be a valuable resource for a long time to come. — Paul W
– Paul W, Commented May 26 at 2:21
maybe you are right but from the AI i have gotten an helfpul answer pushing me in the right direction to solve my problem, not from you, assuming you could have helped me if you had wanted to... and that is the difference i am talking about — Moribundus
– Moribundus, Commented May 26 at 13:22
Please see my response in an answer below. I'm trying to help you. This is a forum where courtesy goes a long way. — Paul W
– Paul W, Commented May 26 at 23:40

Paul W · Accepted Answer · 2025-05-26 23:44:50Z

it would be great if anyone can help me to achieve this or maybe know a simpler way to load data from different views in one table parallel (at the same time)

Since you asked it this way, what I'm suggesting is a simpler way to load data from multiple views concurrently than the method you are trying to use. That's what you asked, that's what I'm suggesting. First in a comment, and now in an answer in order to better demonstrate.

insert /*+ enable_parallel_dml parallel(8) */ into mytable
select * from view_1
union all
select * from view_2
union all
select * from view_3
union all
select * from view_4
union all
select * from view_5
union all
select * from view_6
union all
select * from view_7

The above is one statement, submitted as a unit. Mission accomplished: it loads the table from the various views all at once. A lot simpler than using a PL/SQL engine to achieve parallelism.

Explanation: whatever "degree of parallelism" (DOP) you request (here 8), assuming the instance allows it and you have the CPU resources available, it will allocate twice that many parallel slave processes (two teams of the specified DOP each) which will divide up the work load at the block range level of the underling tables - a lot more granular, and therefore a lot more powerful, than dividing it up by the high-level individual view. It also parallelizes the joins and sort operations they might involve as well.

Further, this also enables "pdml" (parallel DML) which means not only the view queries are parallelized, but the block formatting of the segment being written to (the insert step itself). Space will be allocated above the "high water mark" (HWM), thereby bypassing freespace bitmap lookups and vastly reducing undo writes, as well as postponing index maintenance to the end so indexes don't impact the load step. This is the fastest, most efficient and simplest method of inserting data from multiple sources into a single, pre-existing table all at once.

Furthermore, since 12c, Oracle can run the blocks of a UNION ALL set concurrently, allocating separate parallel slaves to each so that each executes at the same time, rather than one after the other. Initially that required a special hint (pq_concurrent_union) but later versions make this the default - but only when it is beneficial to do so, which is mainly when your set involves distributed queries (to a remote database over a dblink). For most local operations, concurrent union is still possible but not really advantageous, as the point is to move the data the quickest, and that's already being achieved by the low-level parallelism of Oracle PQ/PX out of the box, even if internally it progresses from one UNION ALL set to the next sequentially. As far as you're concerned, it's one statement executed once and it gets done in record time. I assume that's your goal. Well, this is how I'd do it.

Simpler + faster = better.

assuming i put that UNION in an CTAS? Need to create the Table with "PARALLEL" Option? Need to put the DOP at the option too? or is ist enough to specifiy your hint in SELECT /*+ <hints...> */ * FROM (<all union views>) ?
@Moribundus, if you want to create a table with CTAS instead of doing an insert, then simply replace the first line with the appropriate DDL: create table mytable parallel (degree 8) as select ..... No need for any of the hints I suggested above in the SELECT clause in that case.

Moribundus · Accepted Answer · 2025-05-25 13:18:52Z

for anyone that is facing the same problem, i figured out a solution
if using DBMS_PARALLEL_EXECUTE to insert from a view it is necessary to use the bind variables :start_id and :end_id to create chunks and use it in the insert statement, even for the whole view.

here is a solution to create dummy chunks for each view and run as tasks:

for lv in arrLoadViews.first..arrLoadViews.last loop     
      -- create Task
      DBMS_PARALLEL_EXECUTE.CREATE_TASK(strLoadTask);

      -- create one dummy chunk per load view, start_id and end_id must be set
      DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL(
        task_name => strLoadTask, 
        sql_stmt => 'SELECT 1 AS start_id, 1 as end_id FROM DUAL',
        by_rowid => false
        );

      -- create insert statement, start_id and end_id must be set as bind variable from dummy chunk
      stmtInsert := '
        INSERT INTO '||strLoadTable||' 
        SELECT * FROM '||arrLoadViews(lv)||' 
        WHERE :start_id = 1 AND :end_id = 1';

      DBMS_PARALLEL_EXECUTE.RUN_TASK(
        task_name => strLoadTask,
        sql_stmt => stmtInsert,
        language_flag => DBMS_SQL.NATIVE,
        parallel_level => 1
        );

end loop;

Collectives™ on Stack Overflow

DBMS_PARALLEL_EXECUTE insert multiple views in same table parallel

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related