Oracle 中计算密集型函数的最快语言

目前我们有相当多的函数（正态 CDF、逆 CDF、Vasicek 和各种导数）用 PL/SQL 编写，但它们非常慢。

I can get much better performance by streaming the data over a workstation where I have coded out things in C# and then bulk insert the results back. This approach however leaves the network as the bottleneck, it would be much better if I could 'put the mill where the wood is' by having faster functions inside the Oracle DB.

I want to see how I can speed that up by coding it out in either c(++) or Java (or any other alternative you may have). Does anyone here have any experience with this? Hopefully one of you has tried all approaches and can explain which has worked best overall.

Extra complication here is that IT is busy as it is, so if I want a waiver to use some feature on the DB I need to make a solid case. I don;t get to play around much on that box, else I would do that.

我们使用 Oracle Database 11g 企业版 11.2.0.2.0 - 64 位生产版

提前致谢，

格特-扬

编辑

下面是一个函数的示例，即Cody 的 Normal CDF。

它与 的区别cume_dist在于cume_dist查找一组行内的分布。我只需要将概率转换为标准差并转换回来（很多次），就像Excel 中的NORMDIST和函数一样。NORMINV

    function stdnormal_cdf(u number) return number is
  z number;
  y Number;
  begin
    y:=abs(u);
    if y <= 0.6629126073623883041257915894732959743297 then
      z:=y * y;
      y:=u * ((((1.161110663653770e-002 * z + 3.951404679838207e-001) * z + 2.846603853776254e + 001) * z + 1.887426188426510e + 002) * z + 3.209377589138469e + 003)/((((1.767766952966369e-001 * z + 8.344316438579620) * z + 1.725514762600375e + 002) * z + 1.813893686502485e + 003) * z + .044716608901563e + 003);
      return 0.5  +  y ;
    else
      z:=exp(-y * y/2)/2;
      if y <= 5.65685424949238019520675489683879231428 then
        y:=y/1.41421356237309504880168872420969807857;
        y:=((((((((2.15311535474403846e-8 * y + 5.64188496988670089e-1) * y + 8.88314979438837594) * y + 6.61191906371416295e01) * y + 2.98635138197400131e02) * y + 8.81952221241769090e02) * y + 1.71204761263407058e03) * y + 2.05107837782607147e03) * y + 1.23033935479799725e03)/((((((((1.00000000000000000e00 * y + 1.57449261107098347e01) * y + 1.17693950891312499e02) * y + 5.37181101862009858e02) * y + 1.62138957456669019e03) * y + 3.29079923573345963e03) * y + 4.36261909014324716e03) * y + 3.43936767414372164e03) *  + 1.23033935480374942e03);
        y:=z * y;
      else
        z:=z * 1.41421356237309504880168872420969807857/y;
        y:=2/(y * y);
        y:=y * (((((1.63153871373020978e-2 * y + 3.05326634961232344e-1) * y + 3.60344899949804439e-1) * y + 1.25781726111229246e-1) * y + 1.60837851487422766e-2) * y + 6.58749161529837803e-4)/(((((y + 2.56852019228982242) * y + 1.87295284992346047) * y + 5.27905102951428412e-1) * y + 6.05183413124413191e-2) * y + 2.33520497626869185e-3);
        y:=z * (1/1.77245385102123321827450760252310431421-y);
      end if;

      if u < 0 then 
        return y;
      else 
        return 1-y;
      end if;    
    end if;  
  end;

编辑2

好的，这是基准。具有 100k 行的测试表。Oracle 和 F# 之间的函数是彼此非常直接的翻译，并给出相同的结果。

查询：

select 
    sum(get_rwa(approach, exposure_class_code, pd_r, lgd_r, ead_r, maturity_r, net_sale, rwf_r)) 
from functest

• 翻译：12.8 秒
• 本机：13.2 秒
• .Net (F#)：0.04 秒。

This would make the .Net function 320x (!) faster than the Oracle implementation, I really don't understand where this difference could come from. Anything up to 3-10x would seem reasonable. I really think I'm missing something here. Anyone?

In F# I loaded the 100k rows into a List first. (seemed fair, just summing up any other column in Oracle cost 0.06 seconds, so it seemed fair to exclude the data access time in both cases. It takes about 3 sec to load the data into a list, so even if I include the time it takes to open up the connection, execute and stream over the networks etc, then still it's 4x faster.)