在Oracle 11g中优化逐行(游标)处理 [英] Optimizing row by row (cursor) processing in Oracle 11g

问题描述

我必须逐行处理一个大表(2.5B记录)，以便跟踪两个变量.可以想象，这很慢.我正在寻找有关如何调整此过程的想法.谢谢.

I have to process a large table (2.5B records) row by row in order to keep track of two variables. As one can imagine, this is quite slow. I am looking for ideas on how to tune this procedure. Thank you.

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;

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

The issue really comes down to finding efficient sql to track the latest PRC value when BATS='A' and BATS='B' for each BATS='T' record.

推荐答案

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

If I understand your problem correctly, with a table of data like this:

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;

您要使用A和B的最后一个PRC值为每个T插入一行.看起来像这样:

You you want to insert one row for each T, using the last PRC value for A and B. Which would look something like this:

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一起使用，以允许您的进程使用尽可能多的内存，而不会引起其他进程的问题.

With so much data, you'll probably want to use direct path writes and parallelism. The performance will largely depend on whether the sorting for the analytic functions can be done in memory or on disk. Optimizing memory can be very difficult, you'll probably need to work with a DBA to allow your process to use as much memory as possible without causing problems for other processes.

这篇关于在Oracle 11g中优化逐行(游标)处理的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！