在Oracle 11g中优化逐行(游标)处理

Question

为了跟踪两个变量，我必须逐行处理一个大表(2.5B条记录)。可以想象，这是相当缓慢的。我正在寻找关于如何调整这个过程的想法。谢谢你。

declare
    cursor c_data is select /* +index(data data_pk) */ * from data order by data_id;
    r_data c_data%ROWTYPE;
    lst_b_prc number(15,8);
    lst_a_prc number(15,8);
begin
    open c_data;
    loop
        fetch c_data into r_data;
        exit when c_data%NOTFOUND;
        if r_data.BATS = 'B' then
            lst_b_prc := r_data.PRC;
        end if;
        if r_data.BATS = 'A' then
            lst_a_prc := r_data.PRC;
        end if;
        if r_data.BATS = 'T' then
          insert into trans .... lst_a_prc , lst_b_prc      
           end if;
    end loop;
    close c_data;
end;

问题真正归结为找到有效的sql来跟踪最新的PRC值，当BATS='A'和BATS='B'为每个BATS='T'记录

Answer 1

如果我正确理解了您的问题，使用如下的数据表:

create table data as
select 1 data_id, 'T' bats, 1 prc from dual union all
select 2 data_id, 'A' bats, 2 prc from dual union all
select 3 data_id, 'B' bats, 3 prc from dual union all
select 4 data_id, 'T' bats, 4 prc from dual union all
select 5 data_id, 'A' bats, 5 prc from dual union all
select 6 data_id, 'T' bats, 6 prc from dual union all
select 7 data_id, 'B' bats, 7 prc from dual union all
select 8 data_id, 'T' bats, 8 prc from dual union all
select 9 data_id, 'T' bats, 9 prc from dual;

你想为每个T插入一行，使用A和b的最后一个PRC值，看起来像这样:

T data_id   Last A   Last B
---------   ------   ------
1           null     null
4           2        3
6           5        3
8           5        7
9           5        7

这个查询应该可以工作:

select data_id, last_A, last_B
from
(
    select data_id, bats, prc
        ,max(case when bats = 'A' then prc else null end) over
            (order by data_id
             rows between unbounded preceding and current row) last_A
        ,max(case when bats = 'B' then prc else null end) over
            (order by data_id
             rows between unbounded preceding and current row) last_B
    from data
)
where bats = 'T';

对于如此多的数据，您可能希望使用直接路径写入和并行性。性能在很大程度上取决于分析函数的排序是在内存中还是在磁盘上完成。优化内存可能非常困难，您可能需要与DBA合作，以允许您的进程使用尽可能多的内存，而不会给其他进程带来问题。

Answer 2

有几个选项。最重要的是，您可能会为所有插入保留一个巨大的UNDO/REDO日志。您可以偶尔提交您的工作，例如每1000次插入。

另一个选择是使用SQL MERGE语句(或更简单的INSERT .. SELECT ..语句)，这将允许您的Oracle实例对集合而不是单个记录进行操作。选择的执行计划可能会被优化，以获得最佳的INSERT性能。