如何在不使用 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);

I不明白 Oracle 如何在这些条件下有效利用索引。因此每次都必须进行全表扫描。

最后两个条件看起来很合理，但似乎没有什么可以做的。我想重点关注前两个条件，因为我认为它们还有更大的改进空间。

四个条件中的第二个是

instr(Digits, v_MDLDigits) = 1

当且仅当 Digits 以 v_MDLDigits 的内容开头时，此条件成立。更好的写法是

Digits LIKE v_MDLDigits || '%'

在这种情况下使用 LIKE 而不是 INSTR 的优点是 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 条件。

我不认为游标是导致该过程运行缓慢的原因，并且据我所知，没有任何一条语句可以插入到一个表中并从另一个表中删除。为了使这个过程加速，我认为你只需要稍微调整一下查询。