Tables temporaires dans les packages - Oracle

Question

Je suis un peu nouveau dans Oracle. J'essaie de créer un package qui a plusieurs fonctions. Voici le pseudocode de ce que je veux faire

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

L'exécution de ce type de code prend beaucoup de temps parce que TableA et TableB sont des tables très grandes et que je n'obtiens qu'une entrée par enregistrement des tables.

Je voudrais créer une table temporaire (TempTableA, TempTableB) dans le package qui stockera temporairement tous les enregistrements basés sur la date de début et la date de fin, de sorte que lorsque j'essaierai de récupérer la date de transaction et le montant pour chaque enregistrement, je me référerai uniquement aux TempTables (qui sont plus petites que TableA et TableB).

Je veux également prendre en considération le fait que l'identifiant de l'utilisateur n'est pas trouvé dans TableA et TableB. Ainsi, lorsqu'aucun enregistrement n'est trouvé dans TableA et TableB, je veux également que l'entrée figure dans la sortie, mais il est indiqué que l'utilisateur est inactif.

Merci pour votre aide.

Answer 1

SQL est un langage basé sur des ensembles. Il est beaucoup plus efficace d'exécuter une instruction qui renvoie toutes les lignes dont vous avez besoin que d'exécuter plusieurs instructions qui renvoient chacune une seule ligne.

Voici un moyen d'obtenir toutes vos lignes en une seule fois. Elle utilise une expression de table commune parce que vous lisez l'ensemble de la table UserTable et que vous ne devez le faire qu'une seule fois.

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

---

D'ailleurs, soyez prudent avec les tables temporaires. Elles ne sont pas comme les tables temporaires en T-SQL. Ce sont des tables de tas permanentes, ce sont juste leurs données qui sont temporaires. Cela signifie que le remplissage d'une table temporaire est un processus coûteux, car la base de données écrit toutes ces lignes sur le disque. Par conséquent, nous devons être certains que le gain de performance que nous obtenons en lisant un ensemble de données à partir d'une table temporaire vaut le coût de toutes ces écritures.

Ce n'est certainement pas le cas avec votre code. En fait, il est très rare que la réponse à une question de performance soit "Utilisez une table temporaire globale", du moins pas dans Oracle. Il faut améliorer les requêtes et, en particulier, embrasser la joie des ensembles !

Answer 2

Il est probablement préférable de le faire en une seule requête, par exemple :

Select UserTable.UserID, UserTable.UserName, UserTable.UserStatus
      ,TableA.TransactionDate AS ATransactionDate
      ,TableA.Amount          AS AAmount
      ,TableB.TransactionDate AS BTransactionDate
      ,TableB.Amount          AS BAmount
from UserTable
left join TableA
  on (UserTable.UserID = TableA.UserID)
left join TableB
  on (UserTable.UserID = TableB.UserID)
where UserTable.EntryDate between startdate and enddate