DBMS PARALLEL EXECUTE 패키지

1 DBMS PARALLEL EXECUTE 패키지[편집]

1.1 .create_chunks_by_rowid 프로시져[편집]

declare
    l_sql varchar2(500);
    l_chunk_sql varchar2(500);
    l_task_nm varchar2(50):='k_clearance_1';
    l_try number:=0;
    l_status number;
    l_count number:=0;
begin
    select count(1)
    into l_count
    from user_parallel_execute_tasks
    where task_name=l_task_nm;

    --create task if not exist
    if l_count = 0 then
        --create task
        dbms_parallel_execute.create_task(l_task_nm);
    end if;

    --create chunk

    dbms_parallel_execute.create_chunks_by_rowid(
        task_name=>l_task_nm,
        table_owner=>'RXTEAM',
        table_name=>'RPM_STAGE_CLEARANCE_ID_FIX',
        by_row=>TRUE,
        chunk_size=>10000
    );

    l_sql := 'update XXXXXX krsc
        set krsc.lgcy_extr_tmst = sysdate
        where exists (select 1
                      from rxteam.rpm_stage_clearance_id_fix rscf
                      where rscf.rpm_stage_clearance_id=krsc.rpm_stage_clearance_id
                      and rscf.rowid between :start_id and :end_id)';

    --run task        
    dbms_parallel_execute.run_task(task_name=>l_task_nm,
        sql_stmt=>l_sql,
        language_flag=>DBMS_SQL.NATIVE,
        parallel_level=>40
    );

    l_try := 0;
    l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);

    while (l_try < 2 and l_status != DBMS_PARALLEL_EXECUTE.FINISHED) 
    loop
        l_try := l_try + 1;
        DBMS_PARALLEL_EXECUTE.resume_task(l_task_nm);
        l_status := DBMS_PARALLEL_EXECUTE.task_status(l_task_nm);
    end loop;

    --dbms_parallel_execute.drop_task(l_task_nm)
end;
/

DBMS PARALLEL EXECUTE 패키지

DB CAFE

1 DBMS PARALLEL EXECUTE 패키지[편집]

1.1 .create_chunks_by_rowid 프로시져[편집]