You may know that pulling data over a database link is serial, which is a great opportunity to take advantage of DBMS_PARALLEL_EXECUTE.
Here’s an example of how to pull something over a database link in parallel.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 |
declare v_task_name varchar2(128); begin v_task_name := dbms_parallel_execute.generate_task_name; dbms_output.put_line('Task name: [' || v_task_name || ']'); dbms_parallel_execute.create_task(task_name => v_task_name); dbms_parallel_execute.create_chunks_by_sql( task_name => v_task_name , sql_stmt => 'select distinct start_column_id, end_column_id from mytable@other_database' , by_rowid => false ); dbms_parallel_execute.run_task( task_name => v_task_name , sql_stmt => q'< insert into localtable select * from remote_table@other_database where start_column_id between :start_id and :end_id >' , language_flag => dbms_sql.native , parallel_level => 10 ); dbms_output.put_line('Complete at ' || to_char(systimestamp, 'YYYY-MM-DD HH24:MI:SS')); end; / |