Skip to Main Content
  • Questions
  • DBMS_PARALLEL_EXECUTE for partitions on a table

Breadcrumb

May 4th

Question and Answer

Connor McDonald

Thanks for the question, B.

Asked: August 09, 2017 - 3:49 pm UTC

Last updated: August 10, 2017 - 5:01 am UTC

Version: 12.1

Viewed 1000+ times

You Asked

Hi,

One of our projects involves updating data in a partitioned table, this table has a considerable amount of rows and about 254 partitions, We have a merge script that works great when executed by partition. We currently have it setup like the below example, this works but since it processes one partition at a time we were wondering if we could leverage DBMS_PARALLEL_EXECUTE to run this task in parallel ?

declare
l_count number;
l_sql_part_1 varchar2( 3000 ) :=
'merge ' ||
'into scott.employee partition( ';
l_sql_part_2 varchar2( 3000 ) :=
' ) src ' ||
'using ( select employee_id, ' ||
' employee_salary ' ||
' from scott.employee_addendum ) dst ' ||
'on ( src.employee_id = dst.employee_id ) ' ||
'when matched then ' ||
' update ' ||
' set src.employee_salary = dst.employee_salary ' ||
' where src.employee_salary != dst.employee_salary ';

begin
for src in( select partition_name
from all_tab_partitions
where table_owner = 'SCOTT'
and table_name = 'EMPLOYEE'
order by partition_position )
loop
l_statement := l_sql_part_1 ||
src.partition_name ||
l_sql_part_2;
execute immediate l_statement;
l_count := sql%rowcount;
commit;
l_message := 'Merged ' || to_char( l_count, '999,999,999,999' ) || ' row(s) from partition ' || lower( src.partition_name );
dbms_output.put_line( l_message );
end loop;
end;
/

Thanks

BC, MT MI

and Connor said...

Definitely, and it is easy to do with create_chunks_by_sql


declare
  l_task_sql clob;
begin
  l_task_sql := 'select partition_position, partition_position from user_tab_partitions where table_name = 'EMP'';

  dbms_parallel_execute.create_chunks_by_sql(task_name => 'partition_task',
                                             sql_task_sql  => l_task_sql,
                                             by_rowid  => false);
end;
/


The by_rowid set to false means the sql must return start/end numeric values and you'll get one row per partition.

Then you task could be a procedure which takes that partition number, and just does:

select partition_name from user_tab_partitions where table_name = 'EMP' and partition_position = [passed number]

and that will slot nicely into your existing code.

Ping us back if you have any dramas getting it to work.

Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

standards

gh, August 10, 2017 - 5:28 am UTC

.and don't forget to switch the src and dst

Thanks

BC, August 10, 2017 - 11:59 am UTC

Thank you Connor, appreciate the quick response.

And to GH for pointing out the misplaced src and dst.

-BC

need sample query for Merge script for multiple partition parallel processing

Jagdish, August 23, 2017 - 11:37 am UTC

Hi
You are updating data in a partitioned table for multiple partitions at one go.
but you have provided merge script for serial partition
how will your merge script be modified for multiple partitions.
what will be your l_sql_stmt for the run task ?
To execute the below script
dbms_parallel_execute.run_task( 'partition_task',
l_sql_stmt,
dbms_sql.native,
parallel_level => 10 );
Kindly provide inputs
Thanks
jags

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library