performance - 包中的临时表 - Oracle

Question

我是 Oracle 的新手。我正在尝试创建一个具有多种功能的包。这是我想做的伪代码

function FunctionA(UserID, startdate, enddate)
  /* Select TransactionDate, Amount
     from TableA
     where TransactionDate between startdate and enddate
     and TableA.UserID = UserID */
  Return TransactionDate, Amount
end FunctionA

function FunctionB(UserID, startdate, enddate)
  /* Select TransactionDate, Amount
     from TableB
     where TransactionDate between startdate and enddate
     and TableB.UserID = UserID */
  Return TransactionDate, Amount
end FunctionA

TYPE TRANSACTION_REC IS RECORD(
          TransactionDate    DATE,
          TransactionAmt     NUMBER);

function MainFunction(startdate, enddate)
  return TBL
  is
  vTrans TRANSACTION_REC;
begin
  FOR rec IN
    ( Select UserID, UserName, UserStatus
      from UserTable
      where EntryDate between startdate and enddate )
  LOOP
    vTrans := FunctionA(rec.UserID, startdate, enddate)

    if vTrans.TransactionDate is null then
       vTrans := FunctionB(rec.UserID, startdate, enddate)

       if vTrans.TransactionDate is null then
           rec.UserStatus := 'Inactive'
       endif;
    endif;
  END Loop;

  PIPE ROW(USER_OBJ_TYPE(rec.UserID,
                       rec.UserName,
                       rec.UserStatus,
                       vTrans.TransactionDate,
                       vTtans.TransactionAmt));
end MainFunction

运行这种代码需要很长时间，因为 TableA 和 TableB 是一个非常大的表，我只能从表中的每条记录中获取 1 个条目。

我想在包内创建一个临时表(TempTableA、TempTableB)，它将根据开始日期和结束日期临时存储所有记录，这样当我尝试检索每个记录的 TransactionDate 和 Amount 时，我只会引用 TempTables(比 TableA 和 TableB 小)。

我还想考虑是否在 TableA 和 TableB 中找不到 UserID。所以基本上，当在 TableA 和 TableB 中找不到记录时，我也想要输出中的条目，但它表明用户处于非事件状态。

感谢您的帮助。

Answer 1

SQL 是一种基于集合的语言。执行一个返回所需所有行的语句比执行多个每个返回一行的语句要高效得多。

这是一次获取所有行的一种方法。它使用通用表表达式，因为您读取了整个 UserTable，并且您应该只读取一次。

with cte as 
  (select UserID
         , UserStatus
   from UserTable )
select cte.UserID
       , cte.UserStatus
       , TableA.TransactionDate 
       , TableA.Amount  
from cte join TableA   
     on (cte.UserID = TableA.UserID)
where cte.UserStatus = 'A'
and TableA.TransactionDate between startdate and enddate
union
select cte.UserID
       , cte.UserStatus
       , TableB.TransactionDate 
       , TableB.Amount  
from cte join TableB  
     on (cte.UserID = TableB.UserID)
where cte.UserStatus != 'A'
and TableB.TransactionDate between startdate and enddate

---

顺便说一下，要小心临时表。它们不像 T-SQL 中的临时表。它们是永久堆表，只是它们的数据是临时的。这意味着填充临时表是一个昂贵的过程，因为数据库会将所有这些行写入磁盘。因此，我们需要确定通过从临时表读取数据集获得的性能提升值得所有这些写入的开销。

您的代码肯定不会是这种情况。事实上，性能问题的答案很少是“使用全局临时表”，至少在 Oracle 中不是这样。更好的查询是必经之路，尤其是拥抱集合的乐趣!