This example shows the most common usage of this package. After calling the
RUN_TASK Procedure, it checks for errors and re-runs in
the case of error.
DECLARE l_sql_stmt VARCHAR2(1000); l_try NUMBER; l_status NUMBER; BEGIN -- Create the TASK DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask'); -- Chunk the table by ROWID DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'HR', 'EMPLOYEES', true, 100); -- Execute the DML in parallel l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e SET e.salary = e.salary + 10 WHERE rowid BETWEEN :start_id AND :end_id'; DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 10); -- If there is an error, RESUME it for at most 2 times. L_try := 0; L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask'); WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED) LOOP L_try := l_try + 1; DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask'); L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask'); END LOOP; -- Done with processing; drop the task DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask'); END; /
A user can specify their own chunk algorithm by using the CREATE_CHUNKS_BY_SQL Procedure. This example shows that
rows with the same manager_id are grouped together and processed in
one chunk.
DECLARE l_chunk_sql VARCHAR2(1000); l_sql_stmt VARCHAR2(1000); l_try NUMBER; l_status NUMBER; BEGIN -- Create the TASK DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask'); -- Chunk the table by MANAGER_ID l_chunk_sql := 'SELECT distinct manager_id, manager_id FROM employees'; DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_SQL('mytask', l_chunk_sql, false); -- Execute the DML in parallel -- the WHERE clause contain a condition on manager_id, which is the chunk -- column. In this case, grouping rows is by manager_id. l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e SET e.salary = e.salary + 10 WHERE manager_id between :start_id and :end_id'; DBMS_PARALLEL_EXECUTE.RUN_TASK('mytask', l_sql_stmt, DBMS_SQL.NATIVE, parallel_level => 10); -- If there is error, RESUME it for at most 2 times. L_try := 0; L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask'); WHILE(l_try < 2 and L_status != DBMS_PARALLEL_EXECUTE.FINISHED) Loop L_try := l_try + 1; DBMS_PARALLEL_EXECUTE.RESUME_TASK('mytask'); L_status := DBMS_PARALLEL_EXECUTE.TASK_STATUS('mytask'); END LOOP; -- Done with processing; drop the task DBMS_PARALLEL_EXECUTE.DROP_TASK('mytask'); end; /
Executing Chunks in an
User-defined Framework
The user can execute chunks in his own defined framework without using the RUN_TASK Procedure. This example shows how to use GET_ROWID_CHUNK Procedure, EXECUTE
IMMEDIATE, SET_CHUNK_STATUS Procedure to
execute the chunks.
DECLARE l_sql_stmt varchar2(1000); l_try number; l_status number; l_chunk_id number; l_start_rowid rowid; l_end_rowid rowid; l_any_rows boolean; CURSOR c1 IS SELECT chunk_id FROM user_parallel_execute_chunks WHERE task_name = 'mytask' AND STATUS IN (DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR, DBMS_PARALLEL_EXECUTE.ASSIGNED); BEGIN -- Create the Objects, task, and chunk by ROWID DBMS_PARALLEL_EXECUTE.CREATE_TASK ('mytask'); DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID('mytask', 'HR', 'EMPLOYEES', true, 100); l_sql_stmt := 'update /*+ ROWID (dda) */ EMPLOYEES e SET e.salary = e.salary + 10 WHERE rowid BETWEEN :start_id AND :end_id'; -- Execute the DML in his own framework -- -- Process each chunk and commit. -- After processing one chunk, repeat this process until -- all the chunks are processed. -- <> LOOP -- -- Get a chunk to process; if there is nothing to process, then exit the -- loop; -- DBMS_PARALLEL_EXECUTE.GET_ROWID_CHUNK('mytask', l_chunk_id, l_start_rowid, l_end_rowid, l_any_rows); IF (l_any_rows = false) THEN EXIT; END IF; -- -- The chunk is specified by start_id and end_id. -- Bind the start_id and end_id and then execute it -- -- If no error occured, set the chunk status to PROCESSED. -- -- Catch any exception. If an exception occured, store the error num/msg -- into the chunk table and then continue to process the next chunk. -- BEGIN EXECUTE IMMEDIATE l_sql_stmt using l_start_rowid, l_end_rowid; DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('mytask',l_chunk_id, DBMS_PARALLEL_EXECUTE.PROCESSED); EXCEPTION WHEN OTHERS THEN DBMS_PARALLEL_EXECUTE.SET_CHUNK_STATUS('mytask', l_chunk_id, DBMS_PARALLEL_EXECUTE.PROCESSED_WITH_ERROR, SQLCODE, SQLERRM); END; -- -- Finished processing one chunk; Commit here -- COMMIT; END LOOP;
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/13750068/viewspace-1116252/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/13750068/viewspace-1116252/