Discussion :

[labolabs]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[mnitu]

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()

[Waldar]

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

[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) !

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 !!

[Waldar]

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 ?

[labolabs]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

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

[Waldar]

Par exemple :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[mnitu]

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é.

[labolabs]

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

Merci les gars cette astuce !

[labolabs]

Voici le code final:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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;

[mnitu]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
Insert Into Cible
  Select From Table(f_pipeline(arg))