Discussion :

[El Saigneur]

Bonjour

Je fais un algorithme avec une grande quantité d'inserts. A chaque itération j'exécute la requête suivante pour obtenir la clé primaire de mon enregistrement.

Code :

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

1
2
 
select maSequence.nextVal from dual;

Existe-t-il un moyen de récupérer un bloc de valeurs afin de ne pas avoir à exécuter cette requête à chaque boucle ? Cela économiserait un aller - retour à la base de données à chaque itération et améliorerait sans doute les performances.

Merci d'avance pour vos réponses

[pachot]

Bonjour,
Normalement, on ne devrait pas faire un aller-retour pour lire une sequence.
Il suffit d'utiliser maSequence.NEXTVAL dans l'insert plutot que de faire un select from dual avant.
Cordialement,
Franck.

[pachot]

Sinon, pour répondre à

Existe-t-il un moyen de récupérer un bloc de valeurs

c'est assez simple: lorsque tu récupères la valeur n tu as 100 valeurs:
n+0.00 , n+0.01 , n+0.02 , n+0.03 , ... , n+0.99
un décimal de prends pas plus d'un entier
Cordialement,
Franck.

[GoLDoZ]

Sinon, pour répondre à

c'est assez simple: lorsque tu récupères la valeur n tu as 100 valeurs:
n+0.00 , n+0.01 , n+0.02 , n+0.03 , ... , n+0.99
un décimal de prends pas plus d'un entier
Cordialement,
Franck.

Mais dans ce cas, la séquence n'est pas incrémentée, ce qui peut être dangereux selon son utilisation.

[El Saigneur]

Merci pour vos réponses.

Utiliser le fait qu'un réel à 2 décimales ne prenne pas plus de place qu'un entier ne me plaît pas trop. La solution est techniquement bonne mais je ne trouve pas ça très joli. J'aimerais garder des nombres entier dans cette clé primaire.

D'autre part, je ne peux pas utiliser la recherche de séquence directement dans mon INSERT sql. Cette séquence me sert pour valoriser la clé primaire de deux tables qui sont en relation 1-1. La seconde table a sa clé primaire qui est aussi une clé étrangère vers la clé primaire de la première table. Il me faut donc connaître la valeur de la séquence pour la spécifier dans le second insert.

Enfin je réalise ces inserts en Java / JDBC en utilisant un PreparedStatement en batch afin de minimiser les aller retours à la base.

Il n'y a que pour obtenir les séquences que je suis obligé de faire autant d'aller retour en base qu'il y a d'itérations dans mon algo.

N'y a-t-il vraiment pas moyen de demander à la séquence Oracle une collection de valeurs transmises en bloc ?

Merci encore

[GoLDoZ]

Comme l'a dit Pachot dans son premier message, si vous utilisez la séquence directement, vous n'avez pas d'aller-retour supplémentaire:

Code :

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

INSERT INTO ma_table (ID, NOM, ...) VALUES (sequence.nextval, le_nom, ...);

[Waldar]

Il y a un mécanisme de cache avec les séquences, je ne sais pas si ça pourra vous aider.

[mnitu]

...
D'autre part, je ne peux pas utiliser la recherche de séquence directement dans mon INSERT sql. Cette séquence me sert pour valoriser la clé primaire de deux tables qui sont en relation 1-1. La seconde table a sa clé primaire qui est aussi une clé étrangère vers la clé primaire de la première table. Il me faut donc connaître la valeur de la séquence pour la spécifier dans le second insert.
...

Il y a deux solutions:
1) utiliser returning

Code :

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

1
2
 
Insert Into ma_table(id,...) values (sequence.nextval, ...) Returning Id Into ...

2) utiliser pour le deuxième insert la valeur courante de la séquence

Code :

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

1
2
3
 
Insert into ma_table1 values (sequence.nextval)
insert into ma_table2 values (sequence.currval)

[El Saigneur]

mnitu,

Concernant la solution 1) :
Suite à vos conseils sur un Post précédent sur ce forum (merci encore d'ailleurs), j'utilise un PreparedStatement JDBC en batch. Cette technique est très bien expliquée dans l'article que vous m'avez conseillé de lire. Effectivement les performances s'en trouvent nettement améliorées. Cependant en utilisant cette technique de batch, j'insère d'abord mes n lignes dans la table1 puis mes n lignes dans la table2. Quel serait le comportement de Returning id INTO dans ce cas où ma requête insère n lignes d'un coup (n = taille du batch) ? J'ai peur que cela me retourne une collection d'ids en vrac que je ne saurait pas réaffecter aux lignes du second insert en batch dans la table2.

Concernant la solution 2)
Utiliser maSequence.currval n'est-il pas problématique dans un contexte multithreads ? Mon application est une application Web. Plusieurs utilisateurs peuvent lancer l'algorithme en même temps. Dans ce contexte, la valeur retournée par maSequence.currval ne risque-t-elle pas d'être un id d'une ligne insérée par l'autre thread ? Et au-delà de ça, étant donné que j'insère en batch dans la première table, maSequence.currval ne me retournera pas ce qu'il faut pour mes inserts dans me second batch.

Merci encore pour vos indications

C'est dommage car le batch apporte vraiment un gain de performance. Le seul point qui prend vraiment du temps maintenant c'est cette obtention de valeurs de la séquence. Il semblerait donc qu'il n'y ait pas moyen d'obtenir en une seule requête plusieurs valeurs de la séquence. C'est dommage. Avez-vous d'autres idées ?

Merci encore

[GoLDoZ]

La seule solution que je vois, et qui colle exactement à l'esprit de votre demande, c'est une boucle PL/SQL qui récupère les numéros de séquences dans un tableau que vous utilisez ensuite.

Schématiquement (il faut déclarer le tableau, gérer la taille...):

Code :

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

1
2
3
4
FOR i in 1 .. n
LOOP
   SELECT ma_sequence.NEXTVAL INTO mon_tableau(i) FROM DUAL;
END LOOP;

[pachot]

Mais dans ce cas, la séquence n'est pas incrémentée, ce qui peut être dangereux selon son utilisation.

Si, elle est incrémentée. Chaque fois qu'elle est incrémentée de 1, on peut utiliser 100 valeurs décimales qu'on sera les seuls à utiliser.

Utiliser le fait qu'un réel à 2 décimales ne prenne pas plus de place qu'un entier ne me plaît pas trop. La solution est techniquement bonne mais je ne trouve pas ça très joli. J'aimerais garder des nombres entier dans cette clé primaire.

Alors tu peux créer ta sequence en 'increment by 100' et tu auras 100 entiers libres. Clependant, il n'y a vraiement pas beaucoup de différence entre un entier et un décimal car ils sont tous les 2 stockées en virgule flottante.

[GoLDoZ]

Si, elle est incrémentée. Chaque fois qu'elle est incrémentée de 1, on peut utiliser 100 valeurs décimales qu'on sera les seuls à utiliser.

Oups! Exact.

[mnitu]

mnitu,

Concernant la solution 1) :
Suite à vos conseils sur un Post précédent sur ce forum (merci encore d'ailleurs), j'utilise un PreparedStatement JDBC en batch. Cette technique est très bien expliquée dans l'article que vous m'avez conseillé de lire. Effectivement les performances s'en trouvent nettement améliorées. Cependant en utilisant cette technique de batch, j'insère d'abord mes n lignes dans la table1 puis mes n lignes dans la table2. Quel serait le comportement de Returning id INTO dans ce cas où ma requête insère n lignes d'un coup (n = taille du batch) ? J'ai peur que cela me retourne une collection d'ids en vrac que je ne saurait pas réaffecter aux lignes du second insert en batch dans la table2.

Concernant la solution 2)
Utiliser maSequence.currval n'est-il pas problématique dans un contexte multithreads ? Mon application est une application Web. Plusieurs utilisateurs peuvent lancer l'algorithme en même temps. Dans ce contexte, la valeur retournée par maSequence.currval ne risque-t-elle pas d'être un id d'une ligne insérée par l'autre thread ? Et au-delà de ça, étant donné que j'insère en batch dans la première table, maSequence.currval ne me retournera pas ce qu'il faut pour mes inserts dans me second batch.

Merci encore pour vos indications

C'est dommage car le batch apporte vraiment un gain de performance. Le seul point qui prend vraiment du temps maintenant c'est cette obtention de valeurs de la séquence. Il semblerait donc qu'il n'y ait pas moyen d'obtenir en une seule requête plusieurs valeurs de la séquence. C'est dommage. Avez-vous d'autres idées ?

Merci encore

1) Quand vous travaillez en batch vous préparez un tableau des valeurs pour l’insertion. Le Returning devrait vous retourner un tableau des id. Le premier Id corresponde à la première ligne insérée, le deuxième à la deuxième etc. Je pense donc que cette solution devrait aller, mais comme d’habitude un exemple de code pourrait aider à éviter des malentendus.

2) Rien ne vaut plus qu’en petit test
Session 1

Code :

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

1
2
3
4
5
6
7
8
9
10
 
mni@DEV>create sequence test_seq;
 
Séquence créée.
 
mni@DEV>select test_seq.nextval from dual;
 
   NEXTVAL
----------
         1

Session 2

Code :

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

1
2
3
4
5
6
 
mni@DEV>select test_seq.nextval from dual;
 
   NEXTVAL
----------
         2

Et on revient dans la sesion 1

Code :

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

1
2
3
4
5
6
 
mni@DEV>select test_seq.currval from dual;
 
   CURRVAL
----------
         1

Si vous tenez vraiment d'obtenir dans un seule select N valeurs de la sequence, voilà une autre solution

Code :

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

1
2
3
4
5
6
7
8
9
10
 
1* select test_seq.nextval from dual, table(row_generator(1,5))
 
   NEXTVAL
----------
         3
         4
         5
         6
         7

Ici row_generator tiens la place d'une table pivot qui permet de multiplier les nombre des enregistrements de la table qui est la source des données, dual dans ce cas. C'est une fonction pipelined mais cella n'a aucune importance; vous pouvez utiliser n'importe quelle autre table comme multiplicateur d'enregistrements.
Petit remarque cette solutions c'est la même que le returning en fait

[El Saigneur]

J'ai trouvé la solution il y a quelques mois et prends enfin le temps de la partager avec vous. Par exemple, pour récupérer 10000 valeurs de la séquence "maSequence" en une seule requête :

Code :

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

select maSequence.nextval from dual connect by level <= 10000