oracle – PL/SQL或替代方案的数值优化

发布时间：2020-12-12 16:30:12 所属栏目：百科来源：网络整理

导读：我们需要做一些计算繁重的工作来连接Oracle数据库.到目前为止,我们已经在PL / SQL中进行了数值计算,并且很大程度上缺乏性能. 现在我们正在实施一个多级库存模型(https://www.researchgate.net/publication/222409130_Evaluation_of_time-varying_availabilit

我们需要做一些计算繁重的工作来连接Oracle数据库.到目前为止,我们已经在PL / SQL中进行了数值计算,并且很大程度上缺乏性能.

现在我们正在实施一个多级库存模型(https://www.researchgate.net/publication/222409130_Evaluation_of_time-varying_availability_in_multi-echelon_spare_parts_systems_with_passivation)由于问题的严重性,我更关注性能.

我用三种语言实现了部分算法：Fortran(90-2008符合gfortran),Excel中的VBA和PL / SQL,并围绕它进行了一百万次调用测试循环.即使使用binary_double数据类型和使用PLSQL_CODE_TYPE = NATIVE的本机编译(两者都会导致改进),下面的测试代码仍然需要37秒才能运行(Oracle XE 11.2).相比之下,VBA在同一硬件上占用16s和Fortran 1.6s.

虽然要求性能接近Fortran数字可能太过分了(虽然这显然是非常需要的)但我很惊讶即使是不起眼的VBA也会执行PL / SQL.

所以我的问题有两个部分：

>我还可以尝试在Oracle方面进一步提高性能吗？
>如果我们考虑放弃PL / SQL,我们应该考虑哪些替代方案来连接Oracle数据库？虽然接近Fortran速度的性能提升将使我们考虑在应用程序的交互式部分中引入一些繁重的工作,但是繁重的工作将以类似批处理的方式被称为相对较少.我首选的数值工作语言仍然是Fortran,因为它易于实现带有屏蔽的多维数组.

此外,虽然我不是直接批评我的源代码本身,但如果有人能发现任何我可以合并的明显优化,我将不胜感激.

函数timeebo是测试函数,我用双精度简单的selectebo调用它;在SQL Developer中.

create or replace FUNCTION gammln(
    x IN binary_double)
  RETURN binary_double
IS
  --Lanczos' approximation to the Log Gamma function
  gammln binary_double;
  ser binary_double;
  tmp binary_double;
BEGIN
  tmp := x + 5.5;
  tmp :=(x + 0.5) * Ln(tmp) - tmp;
  ser := 1.000000000190015;
  ser := ser + 76.18009172947146 /(x + 1.0) ;
  ser := ser - 86.50532032941677 /(x + 2.0) ;
  ser := ser + 24.01409824083091 /(x + 3.0) ;
  ser := ser - 1.231739572450155 /(x + 4.0) ;
  ser := ser + 1.208650973866179E-03 /(x + 5.0) ;
  ser := ser - 5.395239384953E-06 /(x + 6.0) ;
  RETURN tmp + Ln(2.5066282746310005 * ser / x) ;
END;
/
CREATE OR REPLACE FUNCTION PoissonDist(
    k      IN INTEGER,lambda IN binary_double)
  RETURN binary_double
IS
BEGIN
  RETURN Exp((k * Ln(lambda)) - lambda - gammln(k + 1)) ;
END;
/
CREATE OR REPLACE FUNCTION EBO(
    stock    IN pls_integer,pipeline IN binary_double,accuracy IN binary_double DEFAULT 0.0000000001)
  RETURN binary_double
IS
  i pls_integer;
  EBO binary_double;
  term binary_double;
  temp binary_double;
  PoissonVal binary_double;
  peaked BOOLEAN; --Flag the Poisson curve as having peaked
BEGIN
  EBO        := 0.0;
  IF(pipeline = 0.0) THEN
    RETURN EBO;
  END IF;
--Initialise
i            := 1;
peaked       := false;
PoissonVal   := PoissonDist(stock + 1,pipeline) ;         --Get p() value
IF(PoissonVal < accuracy AND floor(pipeline) > stock) THEN --If p() is very
  -- small...
  i          := floor(pipeline)   - stock;         --Revise i to just below peak of Poisson curve
  PoissonVal := PoissonDist(stock + i,pipeline) ; --Get p() value close to
  -- peak
  temp := PoissonVal *(pipeline / CAST(stock + i + 1 AS binary_double)) ; --
  -- Store poisson value just above peak
  LOOP
    term       := CAST(i AS binary_double) * PoissonVal;
    EBO        := EBO                         + term;
    i          := i                           - 1; --Work backwards
    PoissonVal := PoissonVal                  *(CAST(stock + i + 1 AS DOUBLE
    PRECISION)                                / pipeline) ; --Revise Poisson
    -- value for next time
    EXIT
  WHEN(term < accuracy OR i = 0) ;
  END LOOP;
  i          := 1 + floor(pipeline) - stock;
  PoissonVal := temp;
  peaked     := true;
END IF;
LOOP
  term       := CAST(i AS binary_double) * PoissonVal;
  EBO        := EBO                         + term;
  i          := i                           + 1;
  PoissonVal := PoissonVal                  *(pipeline / CAST(stock + i AS
  binary_double)) ; --Revise Poisson value for next time
  IF(CAST(stock + i AS binary_double) > pipeline) THEN
    peaked                              := true;
  END IF;
  EXIT
WHEN(term < accuracy AND peaked) ;
END LOOP;
IF(EBO < accuracy) THEN
  EBO := 0.0;
END IF;
RETURN EBO;
END;
/
CREATE OR REPLACE FUNCTION timeebo
  RETURN binary_double
IS
  i pls_integer;
  EBOVal binary_double;
  acc binary_double;
BEGIN
  acc := 0.0;
  FOR i IN 1..1000000
  LOOP
    EBOVal := EBO(500,CAST(i AS binary_double) / 1000.0) ;
    acc    := acc                                + EBOVal;
  END LOOP;
RETURN acc;
END;

这不是对OP问题的回答(但它是一个“概念证明”,用于展示他在PL / SQL中所做的事情是如何在普通SQL中完成的).我只使用答案格式,因为我将在下面做的不适合评论.

OP要求了解如何在纯SQL中完成无限级数的求和,达到所需的精度.我将举两个例子.

第一个例子：

具有正项的无限级数：e = sum [j = 0至infinity](1 / factorial(j)).从0到n之和的误差的平凡上限是添加到系列的最后一个项.下面的递归查询(需要Oracle 11.1或更高版本 – 实际上我编写它的方式是11.2,在声明中使用列名,但它可以很容易地改为11.1)计算精确到38位小数的e值(最大精度)在Oracle中可用). e的逆因子序列收敛得非常快;这只需要35步,在我的旧家用电脑上运行不到0.001秒(这只是带键盘的戴尔平板电脑).

编辑：呃！只有我可以发布e = 3.71828的东西！即使在递归查询中我添加了所有条件(包括1/0！),我从1开始求和而不是0.(现在更正,但在纠正之前有这个错误.)

with 
     rec ( j,s,next_term,err ) as (
       select  0,1,2
         from  dual
       union all
       select  j+1,s + next_term,next_term/(j+1),next_term
         from  rec
         where err > power(10,-38) and j < 1000000000
     )
select max(j) as steps,round(max(s),38) as e 
from   rec
;

STEPS                                         E
-----  ----------------------------------------
   35  2.71828182845904523536028747135266249776

第二个例子：

好的,现在让我们采取交替的序列(其中最后一个术语的绝对值总是误差的上限),让我们采取一个非常缓慢收敛的序列：

ln(2)= sum [j = 1到无穷大]((-1)^(j – 1)/ j)

下面的查询计算ln(2),精确到小数点后五位;在这里我们事先知道我们需要100,000步,而我的机器上的计算大约需要1.1秒. (请记住,这是一个非常缓慢融合的系列.)

with
     rec ( j,sgn ) as (
       select  0,1
         from  dual
       union all
       select  j+1,s + sgn / (j+1),-sgn
         from  rec
         where j <= 100000
     )
select 100000 as steps,round(s,5) as ln_2
from   rec
where  j = 100000
;

 STEPS     LN_2
------  -------
100000  0.69314

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!