Fonctions Pipelined et tables mutantes

Bonjour,

Je fais des insertions en utilisant les fonctions Pipelined. J'insère à partir d'une table source T_SOURCE vers une table cible T_CIBLE, qui ont la structure suivante:

Code:

---

1 2 3

CREATE TABLE T_SOURCE (entite char(10), memo char(1000)); CREATE TABLE T_CIBLE (entite char(10), seq number(4,0), memo char(1000));

---

(PK de la table T_CIBLE est entite,seq)

La commande pour insérer est la suivante:

Code:

---

1 2 3 4 5 6 7 8 9 10 11

BEGIN INSERT /*+ PARALLEL(t, 10) */ INTO T_CIBLE t SELECT * FROM TABLE( MyPackage_1.PIPE_CIBLE ( SYSDATE, CURSOR(SELECT /*+ PARALLEL(p, 10) */ * FROM T_SOURCE p))); EXCEPTION WHEN OTHERS THEN /*SORTIR EN ERREUR*/ END;

---

A l'intérieur de ma fonction PIPE_CIBLE, je dois calculer moi-même la séquence qui va être insérée dans T_CIBLE (pour éviter PK), pour ça je suis obligé de faire:

Code:

---

1 2 3 4

SELECT NVL(MAX(seq), 0) + 1 INTO t_cible_record.seq FROM T_CIBLE WHERE entite = t_cible_record.entite;

---

Mais je rencontre le problème de table mutante, vu que j'insère dans T_CIBLE et en même temps j'essaie de faire un SELECT sur la même table !!

Je cherche donc à savoir quel est le moyen le plus performant pour calculer ma séquence sans avoir de contrainte PK.

Merci d'avance

Calculez la séquence dans le select sur la table source en utilisant par exemple un outer join avec la table cible et la fonction analytique row_number()

Comme suggéré ici !
http://www.developpez.net/forums/d91...t/#post5157533

La fonction ROW_NUMBER() ne pourra pas fonctionner dans ce cas, la table pouvant contenir des millions de lignes, alors que la séquence n'est qu'un NUMBER(4,0) !

La solution temporaire que j'ai choisie est de créer une table intermédiaire (similaire à T_CIBLE) et faire comme suit:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14

CREATE TABLE T_CIBLE_TMP (entite char(10), seq number(4,0), memo char(1000));   INSERT /*+ PARALLEL(t, 10) */ INTO T_CIBLE_TMP t SELECT * FROM TABLE( MyPackage_1.PIPE_CIBLE_TMP ( SYSDATE, CURSOR(SELECT /*+ PARALLEL(p, 10) */ * FROM T_SOURCE p)));     INSERT /*+ PARALLEL(t, 10) */ INTO T_CIBLE t SELECT * FROM T_CIBLE_TMP;

---

Dans la fonction PIPE_CIBLE_TMP je fait mon SELECT MAX FROM T_CIBLE pour récupérer la bonne séquence. Cette solution n'est sûrement pas la bonne, mais c'est ce que j'ai trouvé de mieux pour l'instant !!

Citation:

---

Envoyé par labolabs

La fonction ROW_NUMBER() ne pourra pas fonctionner dans ce cas, la table pouvant contenir des millions de lignes, alors que la séquence n'est qu'un NUMBER(4,0) !

---

Aucun rapport !
Comment calculez-vous votre numéro de séquence ?

Normalement on incrémente la séquence à chaque fois que la même entité est ajoutée. Si je veux insérer 2 mémos pour la même entité 'AAA', j'aurais dans ma table:

Code:

---

1 2 3 4 5 6 7

ENTITE || SEQ || MEMO 'AAA' || 1 || 'xxxxxxxxxxxxxxxxxxxx' 'AAA' || 2 || 'xxxxxxxxxxxxxxxxxxxx' 'BBB' || 1 || 'xxxxxxxxxxxxxxxxxxxx' 'AAA' || 3 || 'xxxxxxxxxxxxxxxxxxxx' 'BBB' || 2 || 'xxxxxxxxxxxxxxxxxxxx'

---

Le max sera:

Code:

---

1 2	'AAA' || 9999 || 'xxxxxxxxxxxxxxxxxxxx'

---

Pouvez-vous me donner un example avec ROW_NUMBER() ?

Par exemple :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

select entite, row_number() over(partition by entite order by rownum asc) as seq, memo from ( select 'AAA' as entite, 'xxxxxxxxxxxxxxxxxxxx' as memo from dual union all select 'AAA' , 'xxxxxxxxxxxxxxxxxxxx' from dual union all select 'BBB' , 'xxxxxxxxxxxxxxxxxxxx' from dual union all select 'AAA' , 'xxxxxxxxxxxxxxxxxxxx' from dual union all select 'BBB' , 'xxxxxxxxxxxxxxxxxxxx' from dual ) order by rownum asc;   ENTITE SEQ MEMO AAA 1 xxxxxxxxxxxxxxxxxxxx AAA 2 xxxxxxxxxxxxxxxxxxxx BBB 1 xxxxxxxxxxxxxxxxxxxx AAA 3 xxxxxxxxxxxxxxxxxxxx BBB 2 xxxxxxxxxxxxxxxxxxxx

---

Si la table destination contient déjà la valeur entite avec une séquence quand vous commencez à charger les données, vous avez besoin d'ajouter un delta à la valeur retournée par la fonction row_number(). D'où le jointure externe avec la table cible pour obtenir cette valeur delta pour chaque entité.

C'est cool la fonction ROW_NUMBER() marche très bien :ccool:

Merci les gars cette astuce !

Voici le code final:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15

BEGIN INSERT /*+ PARALLEL(t, 10) */ INTO T_CIBLE t SELECT tab.entite ROW_NUMBER() OVER(PARTITION BY tab.entite ORDER BY ROWNUM) AS seq, tab.memo FROM TABLE( MyPackage_1.PIPE_CIBLE ( SYSDATE, CURSOR(SELECT /*+ PARALLEL(p, 10) */ * FROM T_SOURCE p))) tab LEFT OUTER JOIN T_CIBLE tc ON tc.entite = tab.entite; EXCEPTION WHEN OTHERS THEN /*SORTIR EN ERREUR*/ END;

---

Bon ça insère les lignes sans erreur, mais côté performances j'ai encore des doutes !!

Petit exemple:

• Première exécution: 0 ligne dans T_CIBLE, 3 millions de lignes dans T_SOURCE, durée d'exécution: 13min50s
  
• Deuxième exécution: 3 millions de lignes dans T_CIBLE, 3 millions de lignes dans T_SOURCE, durée d'exécution: 43min12s

Si j'enlève la jointure externe, la première exécution ne fait que 5min10s !!

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

BEGIN INSERT /*+ PARALLEL(t, 10) */ INTO T_CIBLE t SELECT entite ROW_NUMBER() OVER(PARTITION BY entite ORDER BY ROWNUM) AS seq, memo FROM TABLE( MyPackage_1.PIPE_CIBLE ( SYSDATE, CURSOR(SELECT /*+ PARALLEL(p, 10) */ * FROM T_SOURCE p))); EXCEPTION WHEN OTHERS THEN /*SORTIR EN ERREUR*/ END;

---

Les fonctions Pipelined sint loin d’être l'idéal pour faire des jointures avec. Pensez à l'optimiseur, il a besoin d'aide dans ce cas.
Pour que le traitement soit performant la jointure doit se faire à l'intérieur de la fonction pipelined. Le but étant de faire simplement un traitement de type

Code:

---

1 2 3

Insert Into Cible Select From Table(f_pipeline(arg))

---