J'utilise la collecte en vrac pour améliorer le temps d'exécution. Lorsque je n'utilise pas de collecte de vrac, il s'exécute en 4 minutes. Mais lorsque j'utilise la collecte en vrac, il n'y a pas de sortie, aucun message d'erreur n'est affiché dans la console. Je peux voir un fichier de bobine vierge créé. S'il vous plaît laissez-moi savoir si j'ai utilisé la collecte en vrac de manière incorrecte, pouvons-nous également utiliser cette clause dans une déclaration de sélection avec limite? Le tableau consiste en un maximum de 1 million d'enregistrements.
SET SERVEROUTPUT ON FORMAT WRAPPED
SET VERIFY OFF
SET FEEDBACK OFF
SET TERMOUT OFF
SPOOL C:\Temp\spool_1.txt
DECLARE
cursor c2 is (
select count(distinct e.cdb_pref_event_id)
,e.supp_cd
from (select distinct eh.cdb_customer_id cdb_customer_id
,eh.cdb_pref_event_id cdb_pref_event_id
,eh.supp_cd supp_cd
from (select *
from cdb_stg.cpm_pref_event_stg_arc
where trunc(load_date) = trunc(sysdate - 1)) eh
Left outer join cdb_admin.cpm_pref_result er on (eh.cdb_customer_id =
er.cdb_customer_id and
eh.cdb_pref_event_id =
er.cdb_pref_event_id)
where er.cdb_pref_event_id is null
and er.cdb_customer_id is null) r
join cdb_admin.cpm_pref_event_exception e on (r.cdb_customer_id =
e.cdb_customer_id and
r.cdb_pref_event_id =
e.cdb_pref_event_id)
group by e.supp_cd);
TYPE totalprefresults is table of NUMBER(20);
TYPE supcd_1 is table of cdb_admin.cpm_pref_event_stg.supp_cd%TYPE;
total_prefresults totalprefresults;
supcd1 supcd_1;
--Total_prefresults NUMBER(20);
--SUPCD1 CDB_ADMIN.CPM_PREF_EVENT_STG.supp_cd%TYPE;
profile_counts NUMBER(20);
iter Integer := 0;
BEGIN
select count(distinct cdb_customer_id)
into profile_counts
from cdb_admin.cpm_pref_event_exception h
where cdb_customer_id in
(Select distinct e.cdb_customer_id
from (Select distinct eh.cdb_customer_id cdb_customer_id
,eh.cdb_pref_event_id cdb_pref_event_id
,eh.supp_cd supp_cd
from (select *
from cdb_stg.cpm_pref_event_stg_arc
where trunc(load_date) = trunc(sysdate - 1)) eh
Left outer join cdb_admin.cpm_pref_result er on (eh.cdb_customer_id =
er.cdb_customer_id and
eh.cdb_pref_event_id =
er.cdb_pref_event_id)
where er.cdb_pref_event_id is null
and er.cdb_customer_id is null) r
join cdb_admin.cpm_pref_event_exception e on (r.cdb_customer_id =
e.cdb_customer_id and
r.cdb_pref_event_id =
e.cdb_pref_event_id)
where e.supp_cd = 'PROFILE-NOT-FOUND')
and h.supp_cd != 'PROFILE-NOT-FOUND';
dbms_output.put_line('TOTAL EVENTS VALIDATION');
dbms_output.put_line('-------------------------------------------------------------');
dbms_output.put_line('');
dbms_output.put_line(rpad('Pref_Counts', 25) || rpad('Supp_CD', 25));
OPEN c2;
LOOP
FETCH c2 BULK COLLECT
INTO total_prefresults
,supcd1 limit 100;
EXIT WHEN c2%NOTFOUND;
dbms_output.put_line(rpad(total_prefresults, 25) || rpad(supcd1, 25));
IF (supcd1 = 'PROFILE-NOT-FOUND')
then
dbms_output.put_line('');
dbms_output.put_line('Profile not found records count : ' ||
total_prefresults);
dbms_output.put_line(profile_counts ||
' : counts moved to other exceptions ');
dbms_output.put_line((total_prefresults - profile_counts) ||
' : are still in Profile_not_found exception');
END IF;
iter := iter + 1;
END LOOP;
CLOSE c2;
dbms_output.put_line('');
dbms_output.put_line('Number of missing Records: ' || iter);
END;
/
SPOOL OFF
3 Réponses :
J'ai recadré votre code à partir de Ouvrir C2; code> à Fermer C2; CODE> Collecte en vrac code> doit être exécuté pour stocker tous les Données uniquement dans la collection une seule fois (en une fois), puis cette collection peut être utilisée à l'aide de l'index (c'est-à-dire I dans le cas suivant) dans pour boucle code> comme suit: p> OPEN C2;
FETCH C2 BULK COLLECT INTO
TOTAL_PREFRESULTS,
SUPCD1;
--EXIT WHEN C2%NOTFOUND;
CLOSE C2;
-- To list down all the values before processing the logic
FOR I IN TOTAL_PREFRESULTS.FIRST..TOTAL_PREFRESULTS.LAST LOOP
DBMS_OUTPUT.PUT_LINE(RPAD(TOTAL_PREFRESULTS(I), 25)
|| RPAD(SUPCD1(I), 25));
END LOOP;
FOR I IN TOTAL_PREFRESULTS.FIRST..TOTAL_PREFRESULTS.LAST LOOP
IF ( SUPCD1(I) = 'PROFILE-NOT-FOUND' ) THEN
DBMS_OUTPUT.PUT_LINE('');
DBMS_OUTPUT.PUT_LINE('Profile not found records count : ' || TOTAL_PREFRESULTS(I));
DBMS_OUTPUT.PUT_LINE(PROFILE_COUNTS || ' : counts moved to other exceptions ');
DBMS_OUTPUT.PUT_LINE((TOTAL_PREFRESULTS(I) - PROFILE_COUNTS)
|| ' : are still in Profile_not_found exception');
END IF;
ITER := ITER + 1;
END LOOP;
Je pense que le goulot d'étranglement est cette condition: Avez-vous un index sur où tronc (load_date) = tronc (sysdate - 1) trunc (load_date) ? Soit créer un index basé sur la fonction sur trunc (load_date) ou si vous avez déjà un index sur load_date , essayez distinct est vraiment nécessaire. Retirez-les si possible.
La collecte en vrac peut fournir un gain de performance considérable. Cependant, il y a un couple gotchas impliqué. de
Tout d'abord, il y a la différence de la signification de% sans contenu.
Sur un curseur standard% NotFound signifie que toutes les lignes ont déjà été récupérées et qu'il n'y a plus. Avec la masse collectionne ces changements à «Il y avait des lignes insuffisantes pour atteindre la limite spécifiée (le cas échéant).
Cela ne signifie pas qu'il n'y a pas de rangées extraite, la limite spécifiée n'a pas été atteinte. Par exemple, si votre limite est de 100 et que la récupération n'a récupéré que 50 alors% de ne pas retournerait vrai. C'est là que le guide référencé échoue.
de
La seconde est ce qui se passe sans la clause de limite: toutes les lignes du curseur sont renvoyées dans une mémoire partagée (PGA?). Alors, quel est le problème avec ça.
S'il y a 100 rangées ou 1000, alors probablement que vous êtes k, mais supposons qu'il y ait des rangées de 100 000 ou 1 m, elles sont toujours toutes chargées en mémoire. Enfin (au moins pour l'instant) lorsque la clause de limite est utilisée, l'ensemble du processus de récupération + doit être enfermé dans une boucle ou que vous ne traitez que la première extraction - ce qui ne signifie que le nombre limité spécifié des rangées - peu importe le nombre de lignes. Un autre point où le guide référencé échoue.
Le squelette suivant accueille ce qui précède.
declare
max_bulk_rows constant integer := 1000; -- define the max number of rows for each fetch ...
cursor c_bulk is(
Select ... ;
type bulk_row_t is table of c_bulk%rowtype;
bulk_row bulk_row_t;
Begin
open c_bulk;
loop
fetch c_bulk -- fill buffer
bulk collect into bulk_row
limit max_bulk_row;
for i in bulk_row.first .. bulk_row.last -- process each row in buffer
loop
"process individual row here"
end loop;
foreach ... -- bulk output of rows here is needed.
exit when bulk_row.count < max_bulk_row; -- exit process loop if all rows processed
end loop ; -- loop back and fetch next buffer if needed
close c_bulk;
...
end;
Merci pour vos contributions. Malheureusement, je n'ai pas la possibilité de marquer plusieurs réponses.
Aucun problème. Il suffit de tracer le modèle dans vos grandes trucs pour la prochaine fois.
total_prefresults etsuppcd1 est la collecte et pourrait contenir plus d'un élément. Vous pouvez ajouter une boucle à l'intérieur de votre première boucle et itérale sur les éléments de la collection. Mais je pense que vous n'améliorerez pas votre code de cette façon.