使用批量收集时设置LIMIT的值 [英] Setting a value for LIMIT while using bulk collect

问题描述

我想知道我们是否有什么技术可以计算需要为批量收集操作的LIMIT子句设置的值. 例如，在下面的示例中，假设我们的光标有1000万条记录.为达到最佳性能，我们可以为LIMIT子句设置的值是多少.有什么办法可以计算出来.

I wanted to know if we have any technique by which we can calculate the value which needed to be set for a LIMIT clause of bulk collect operation. For example below, lets say our cursor has 10 Million records..What is the value which we can set for LIMIT clause to have optimum performance. Is there any way we can calculate it.

decalre
cursor c_emp is <some select query>

var  <variable> ;

begin
     open c_emp;
       loop
           fetch c_emp bulk collect into var limit 2;
           exit when c_emp%NOTFOUND;
      end loop;
     close c_emp;
  end;

推荐答案

在游标FOR LOOP中使用隐式游标.它使代码更简单，默认值100几乎总是足够好.

Use an implicit cursor in a cursor FOR LOOP. It makes the code simpler and the default value of 100 is almost always good enough.

我已经看到很多人为此花费很多时间.如果您想到为什么批量收集可以提高性能，那么您会理解为什么大量无用的原因.

I've seen a lot of people waste a lot of time worrying about this. If you think about why bulk collect improves performance you will understand why large numbers won't help.

批量收集通过减少SQL和PL/SQL之间的上下文切换来提高性能.想象一下极不可能发生的最坏情况，其中上下文切换用尽了所有运行时间.限制2消除了50％的上​​下文切换； 10消除90％； 100消除了99％，以此类推.将其绘制出来，您会发现寻找最佳极限尺寸是不值得的:

Bulk collect improves performance by reducing the context switches between SQL and PL/SQL. Imagine the highly-unlikely worst case scenario, where context switching uses up all the run time. A limit of 2 eliminates 50% of the context switches; 10 eliminates 90%; 100 eliminates 99%, etc. Plot it out and you'll realize it's not worth finding the optimal limit size:

使用默认值.花时间担心更重要的事情.

Use the default. Spend your time worrying about more important things.

这篇关于使用批量收集时设置LIMIT的值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！