Incremental (or any other way) large INSERT

Paulzip wrote:

Here's an approach, but there is no substitute for pure SQL.  There's always a risk that committing during a cursor loop may lead to snapshot too old errors, if other transactions have changed the DEST_TABLE.  Locking DEST_TABLE exclusively may minimize that risk.  Personally, I'd do insert /* +APPEND */ select into and leave it running.

declare
  type TTableRows is table of DEST_TABLE%ROWTYPE;
  vTableRows TTableRows;
  cursor cur is 
    select  attr1, attr2, attrn
    from SRC_TABLE;
  vRows integer := 0;
begin
  open cur;
  loop
    fetch cur bulk collect into vTableRows limit 500;
    forall i in 1..vTableRows.Count
      insert into DEST_TABLE values vTableRows(i);
    exit when vTableRows.count = 0;
    vRows := vRows + vTableRows.Count;
    if vRows > 100000 then
      commit;
      vRows := 0;
    end if;   
  end loop;
  close cur;
  commit;
end;
/

This approach and code are wrong. Wrong. And wrong.

If you want to do this via PL/SQL, then use PL/SQL to manage the processing. Not to replace the SQL processing and relegating the SQL language to doing I/O (read and write) calls only.

Using a single SQL statement (INSERT..SELECT or MERGE) and executing it for row id ranges in staggered parallel (e.g. 2 processes at a time) via DBMS_PARALLEL_EXECUTE.

Example:

declare
        taskName        varchar2(30) default 'PQ-task-1';
        parallelSql    varchar2(1000);
begin
        --// create trask
        DBMS_PARALLEL_EXECUTE.create_task( taskName );

        --// chunk the table by rowid ranges
        DBMS_PARALLEL_EXECUTE.create_chunks_by_rowid(
                task_name => taskName,
                table_owner => user,
                table_name => 'SOURCETABLE',
                by_row => true,
                chunk_size => 100000
        );

        --// create insert..select statement to copy a chunk of rows
        parallelSql := 'insert into targetTable select .. from sourceTable where rowid between :start_id and :end_id';

        --// run the task using 2 parallel processes at a time
        DBMS_PARALLEL_EXECUTE.Run_Task(
                task_name => taskName,
                sql_stmt => parallelSql,
                language_flag => DBMS_SQL.NATIVE,
                parallel_level => 2
        );

        --// wait for it to complete
        while DBMS_PARALLEL_EXECUTE.task_status( taskName ) != DBMS_PARALLEL_EXECUTE.Finished loop
                DBMS_LOCK.Sleep(10);
        end loop;

        --// remove task
        DBMS_PARALLEL_EXECUTE.drop_task( taskName );
end;
/

This is SIGNIFICANTLY:

- faster (allows parallel processing)

- more flexible (you define the average chunk size to process and how many chunks in parallel)

- safer (failed processes can safely be executed again)

- more manageable (via predefined USER_* views for DBMS_PARALLEL_EXECUTE processes, workloads, and status)

Incremental commits is a FAILED approach in Oracle. Always.