PLSQL:存储过程的替代方案，以实现最佳性能 [英] PLSQL: Alternative to Stored Procedure for optimal performance

问题描述

我被要求提供一种执行以下任务的最佳方法，

I have been asked to provide an optimal approach to perform following task,

我们有一个视图，该视图从多个表中提取数据，并且我们必须对这些提取的数据执行一些业务逻辑，然后将处理后的数据插入另一个表中.这里的问题是，视图非常复杂，并且在执行时会提取4000万条记录，这本身需要花费很多时间来执行.但是在众多记录中，我们必须对大约2500万条记录执行逻辑.

We have a view which extract data from multiple tables and we have to perform some business logic to these extracted data and then insert the processed data into an another table. Problem here is that the View is very complicated and on execution extracts 40 Millions of records which itself takes a lots of time in execution. But out of these many records we have to perform logic on approx 25 millions of records.

为此，我建议了一种方法，可以将这2400万条记录插入到Global Temporary表中，并在该临时表上执行所有业务逻辑，然后将处理后的数据插入到最终表中.

For this i have suggested an approach to insert these 24 Millions record in a Global Temporary table and perform all business logic on that temp table and then insert the processed data into the final table.

我请您建议这种方法是否适合这种批量数据，否则我们仍然可以更好地优化Oracle概念来执行此任务.在此之前，我曾从事过TSQL的工作，而PLSQL对我来说是新手，因此任何建议都将非常有帮助.谢谢

I request you to please suggest if this approach is fine with this bulk data or we can still perform this task in better optimal Oracle concept. I have worked on TSQL before this and PLSQL is new to me, hence any suggestion would be really helpful. Thanks

推荐答案

在Oracle中，通常不需要为此类事情使用全局临时表，而可以对数组使用批量处理:

In Oracle you don't normally need to use global temporary tables for this sort of thing, rather you can use bulk processing with arrays:

declare
   cursor c is
      select col1, col2 from my_view;
   type t is table of c%rowtype;
   array t;
begin
   open c;

   loop
      fetch c bulk collect into array limit 1000;
      exit when array.count = 0;

      for i in 1..array.count loop
         null; -- Perform business logic on array(i) here
      end loop;

      forall i in 1..array.count
         insert into final_table (col1, col2)
            values (array(i).col1, array(i).col2);

   end loop;

   close c;
end;

那只是一个最小的示例-请参见此文章以获取更多详细信息.

That's just a minimal example - see this article for more details.

这篇关于PLSQL:存储过程的替代方案，以实现最佳性能的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！