Execute Oracle For Loop in parallel

Question

I am looping through a list of table and updating a list of column in each table. Is it possible to execute the loop parallely, that is update more than one table at a time.

  FOR Table_rec IN Table_list_cur
  LOOP
     --Check if the table is partitioned
     IF Check_if_partitioned (Table_rec.Table_name, Table_rec.Owner)
     THEN
        --If Yes, loop through each parition
        EXECUTE IMMEDIATE
              'Select partition_name from USER_TAB_PARTITIONS where table_name = '''
           || Table_rec.Table_name
           || ''' and owner = '''
           || Table_rec.Owner
           || ''''
           BULK COLLECT INTO L_part;

        FOR I IN L_part.FIRST .. L_part.LAST
        LOOP
           --Update each parition
           DBMS_OUTPUT.Put_line ('V_sql_stmt' || V_sql_stmt);
           V_sql_stmt :=
                 'UPDATE /*+ PARALLEL(upd_tbl,4) */ '
              || Table_rec.Table_name
              || ' PARTITION ('
              || L_part (I)
              || ') upd_tbl'
              || ' SET '
              || V_sql_stmt_col_list;
           DBMS_OUTPUT.Put_line ('V_sql_stmt' || V_sql_stmt);

           EXECUTE IMMEDIATE V_sql_stmt;
         END IF;
        END LOOP;
  END LOOP;

On an unrelated note - don't forget to alter session enable parallel dml; When parallelism works you'll also need to commit between DML. And I'd recommend replaced /*+ PARALLEL(upd_tbl, 4) */ with the simpler 11gR2 syntax - /*+ PARALLEL(4) */. — Jon Heller
– Jon Heller, Commented Aug 13, 2015 at 21:45

Justin Cave · Accepted Answer · 2015-08-13 21:44:35Z

Not directly, no.

You could take the guts of your loop, factor that out into a stored procedure call, and then submit a series of jobs to do the actual processing that would run asynchronously. Using the dbms_job package so that the job submission is part of the transaction, that would look something like

CREATE OR REPLACE PROCEDURE do_update( p_owner      IN VARCHAR2,
                                       p_table_name IN VARCHAR2 )
AS
BEGIN
  <<your dynamic SQL>>
END;

and then run the loop to submit the jobs

FOR Table_rec IN Table_list_cur
LOOP
   --Check if the table is partitioned
   IF Check_if_partitioned (Table_rec.Table_name, Table_rec.Owner)
   THEN
     dbms_job.submit( l_jobno,
                      'begin do_update( ''' || table_rec.owner || ''', ''' ||  table_rec.table_name || '''); end;' );
   END IF;
END LOOP;                         
commit;

Once the commit runs, the individual table jobs will start running (how many will run is controlled by the job_queue_processes parameter) while the rest are queued up.

Now, that said, your approach seems a bit off. First, it's almost never useful to specify a partition name explicitly. You almost certainly want to submit a single UPDATE statement, omit the partition name, and let Oracle do the updates to the various partitions in parallel. Running one update statement per partition rather defeats the purpose of partitioning. And if you really want 4 parallel threads for each partition, you probably don't want many of those updates running in parallel. The point of parallelism is that one statement can be allowed to consume a large fraction of the system's resources. If you really want, say 16 partition-level updates to be running simultaneously and each of those to run 4 slaves, it would make far more sense to let Oracle run 64 slaves for a single update (or whatever number of slaves you really want to devote to this particular task depending on how many resources you want to leave for everything else the system has to do).

Collectives™ on Stack Overflow

Execute Oracle For Loop in parallel

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related