如何在不使用Cursor的情况下编写以下pl/sql块？

Question

我在pl/sql块中写了一个游标。如果它有更多的记录，这个块花费很多时间。 如何在没有光标的情况下编写此代码或者是否有其他可以减少时间的替代方法？ 是否有任何替代查询来执行插入到一个表中并使用单个查询从另一个表中删除？

DECLARE 
     MDLCursor SYS_REFCURSOR; 
    BEGIN 
     open MDLCursor for 
     select dc.dest_id, dc.digits, dc.Effectivedate, dc.expirydate 
      from DialCodes dc 
     INNER JOIN MDL d 
      ON dc.Dest_ID = d.Dest_ID 
      AND d.PriceEntity = 1 
      join sysmdl_calltypes s 
      on s.call_type_id = v_CallType_ID 
      and s.dest_id = dc.Dest_ID 
      and s.call_type_id not in 
       (select calltype_id from ignore_calltype_for_routing) 
     order by length(dc.digits) desc, dc.digits desc; 
     loop 
     fetch MDLCursor 
      into v_mdldest_id, v_mdldigits, v_mdlEffectiveDate, v_mdlExpDate; 
     insert into tt_pendingcost_temp 
      (Dest_ID, 
      Digits, 
      CCASDigits, 
      Destination, 
      tariff_id, 
      NewCost, 
      Effectivedate, 
      ExpiryDate, 
      previous, 
      Currency) 
      select v_mdldest_id, 
       Digits, 
       v_mdldigits, 
       Destination, 
       tariff_id, 
       NewCost, 
       Effectivedate, 
       ExpiryDate, 
       previous, 
       Currency 
      FROM tt_PendingCost 
      where substr(Digits, 1, 2) = substr(v_MDLDigits, 1, 2) 
      and instr(Digits, v_MDLDigits) = 1 
      and v_mdlEffectiveDate <= effectivedate 
      and (v_mdlExpDate > effectivedate or v_mdlExpDate is null); 
     if SQL%ROWCOUNT > 0 then 
      delete FROM tt_PendingCost 
      where substr(Digits, 1, 2) = substr(v_MDLDigits, 1, 2) 
      and instr(Digits, v_MDLDigits) = 1 
      and v_mdlEffectiveDate <= effectivedate 
      and (v_mdlExpDate > effectivedate or v_mdlExpDate is null); 
     end if; 
     exit when MDLCursor%NOTFOUND; 
     end loop; 
     close MDLCursor; 
    END; 

Answer 1

我没有你的表格和你的数据，所以我只能猜测几件事会让你放慢速度。

首先，光标中使用的查询中有一个ORDER BY子句。如果这个查询返回很多行，那么Oracle必须先取回所有行并将它们排序，然后才能返回第一行。如果这个查询通常会返回很多结果，并且您并不特别需要它来返回排序结果，那么如果您放弃ORDER BY，则可能会发现您的PL/SQL块的速度有点提升。这样，您就可以开始从游标中获取结果，而无需获取所有结果，将它们存储在某个地方并首先对其进行排序。

其次，下面是你INSERT INTO ... SELECT ...和DELETE FROM ...语句使用的WHERE条款：

where substr(Digits, 1, 2) = substr(v_MDLDigits, 1, 2) 
     and instr(Digits, v_MDLDigits) = 1 
     and v_mdlEffectiveDate <= effectivedate 
     and (v_mdlExpDate > effectivedate or v_mdlExpDate is null); 

我不知道甲骨文如何能够有效使用索引与任何条件。因此每次都必须进行全表扫描。

最后两个条件似乎是合理的，似乎没有什么可以与他们完成。我想专注于前两个条件，因为我认为他们有更多的改进空间。

的四个条件的第二个是

instr(Digits, v_MDLDigits) = 1 

此条件成立当且仅当Digits打头的v_MDLDigits内容。写这更好的办法是

Digits LIKE v_MDLDigits || '%' 

在这种情况下，而不是使用INSTR的LIKE的好处是，Oracle可以使用LIKE时使用索引。如果您在Digits列中有索引，则Oracle可以将其用于此查询。然后，Oracle将能够专注于以v_MDLDigits中的数字开头的那些行，而不是执行全表扫描。

第一的四个条件是：

substr(Digits, 1, 2) = substr(v_MDLDigits, 1, 2) 

如果v_MDLDigits具有长度为至少2，和中的所有条目Digits列也有长度至少为2，则由于这是暗示该条件是多余在前一个我们看过。

我不知道为什么你会有这样的条件。我能想到为什么你可能会遇到这种情况的唯一原因是你的功能索引substr(Digits, 1, 2)。如果没有，我会试图完全删除这个substr条件。

我不认为游标是什么让这个过程运行缓慢，并没有单一的声明，我知道可以插入到一个表中，并从另一个表中删除。为了加快这个过程，我想你只需要调整一下查询。