Discussion :

[Baptiste443]

Bonjour,

ma question se trouve en titre de cette discussion : comment réserver ses ids ?

Version utilisée : Oracle 12C

Enjeux : je dispose d'un modèle de données relationnel avec de nombreuses clés étrangères car j'essaie au maximum de respecter les formes normales, cependant lors de certaines insertions cela produit de très longues requêtes avec beaucoup de condition d'égalité sur du VARCHAR. Hors, afin de réduire le risque d'erreur et de raccourcir mes codes je voudrais pouvoir réserver à l'avance une suite d'id afin d'avoir, par exemple, tous les libellés qualifiant l'action de mes utilisateurs entre 1 et 5. Ainsi, au lieu de faire une recherche sur la valeur je ferai une recherche sur l'id, ce qui serait plus rapide, plus court à écrire et moins sujet aux erreurs (notamment dues à la casse).

Objectif : le code doit donc permettre de réserver une plage d'id tout en permettant l'insertion dans la même table et par un autre utilisateur d'autres valeurs (avec une réservation d'ids). Il ne doit pas y avoir de doublons d'id ou de valeur.

J'ai trouvé une solution que je vous présente ci-dessous, cependant j'y utilise une boucle séquentielle, ce qui n'est pas l'idéal pour de gros volume de données. J'aimerai donc savoir si vous connaissez une solution ou si vous auriez des pistes avec un traitement ensembliste.

Je précise également que j'utilise le type de donnée NUMBER GENERATED BY DEFAULT AS IDENTITY afin d'utiliser l'auto-incrémentation et de pouvoir modifier la séquence d'auto-incrémentation à ma guise.

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
-- Création des tables
CREATE TABLE "G_GEO"."TEMP_LIBELLE"(
    "OBJECTID" NUMBER(38,0) GENERATED BY DEFAULT AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 43 NOCACHE  NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "VALEUR" VARCHAR2(4000 BYTE)
);
 
CREATE TABLE "G_GEO"."TEMP_IMPORT"(
    "OBJECTID" NUMBER(38,0) GENERATED ALWAYS AS IDENTITY,
    "VALEUR" VARCHAR2(4000 BYTE)
);
-- 1. Code permettant de réserver une plage d'id et d'insérer une liste de valeur dans la même transaction ;
SET SERVEROUTPUT ON
DECLARE
    v_id_max NUMBER(38,0);
    v_nb_id_reserve NUMBER(38,0);
    v_id_reserve NUMBER(38,0);
    v_nom_sequence VARCHAR2(50);
 
BEGIN
	SAVEPOINT POINT_SAUVERGARDE_RESERVATION_ID;
-- Identification des libellés présents en base ou non
UPDATE G_GEO.TEMP_IMPORT a
    SET a.EN_BASE = CASE
                        WHEN a.VALEUR IN(SELECT b.VALEUR FROM G_GEO.TEMP_LIBELLE b)
                            THEN 1 -- si la valeur est déjà en base alors 1
                        ELSE
                            0 -- si la valeur n'est pas en base alors 0
                    END;
 
-- Décompte du nombre d'objet/de valeurs à insérer en base
SELECT
    COUNT(OGR_FID)
    INTO v_nb_id_reserve
FROM
    G_GEO.TEMP_IMPORT
WHERE
    EN_BASE = 0;
 
-- Sélection du nom de la séquence d'incrémentation
SELECT 
    SEQUENCE_NAME
    INTO v_nom_sequence
FROM user_tab_identity_cols
WHERE TABLE_NAME = 'TEMP_LIBELLE';
 
-- Sélection de la prochaine valeur de séquence de la table d'insertion
SELECT
    LAST_NUMBER
    INTO v_id_max
FROM
    ALL_SEQUENCES
WHERE
    SEQUENCE_NAME = v_nom_sequence;
 
-- Création du nouvel id à partir duquel faire repartir l'auto-incrémentation de la PK.
    v_id_reserve := v_nb_id_reserve + v_id_max
-- Modification de la valeur de départ de la séquence d'auto-incrémentation via du SQL dynamique
EXECUTE IMMEDIATE 'ALTER TABLE G_GEO.TEMP_LIBELLE MODIFY OBJECTID GENERATED BY DEFAULT AS IDENTITY (START WITH ' || v_id_reserve || ')';
 
-- Insertion des valeurs dans la table. L'incrémentation de la PK se fait via une boucle, c'est-à-dire une structure séquentielle
    FOR i IN(SELECT VALEUR FROM TEMP_MIBELLE_POINT_VIGILANCE WHERE EN_BASE = 0) LOOP
        INSERT INTO G_GEO.TEMP_LIBELLE(objectid, valeur)
        VALUES(v_id_max, i.valeur);
        v_id_max := v_id_max + 1;
    END LOOP;
 
-- Le code qui suit permet de connaître le nombre d'ids réservés et la valeur à partir de laquelle l'auto-incrémentation reprend
DBMS_OUTPUT.PUT_lINE('Nombre d''id à réserver : '|| v_nb_id_reserve || ' - id de reprise de l''incrémentation : ' || v_id_reserve);
-- En cas d'erreur un rollback est effectué, permettant de revenir à l'état de la table précédent la tentative d'insertion. Dans ce cas le LAST_NUMBER de la séquence d'auto-incrémentation n'est pas modifié.
EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.put_line('une erreur est survenue, un rollback va être effectué: ' || SQLCODE || ' : '  || SQLERRM(SQLCODE));
    ROLLBACK TO POINT_SAUVERGARDE_RESERVATION_ID;
END;

Je pensais pouvoir utiliser NEXTVAL mais il me semble que cette pseudo-colonne n'est pas spécifique à la session, contrairement à CURRVAL, mais commun à toutes, ce qui empêche de faire deux réservations d'ids en même temps.

Merci pour votre aide,
Baptiste443

[vanagreg]

Bonjour,

Je ne pense pas avoir tout compris, mais réserver des ids c'est déjà ce que font très bien les séquences, avec la gestion d'un cache.
Peux-tu expliquer le problème de départ?

[Baptiste443]

Bonjour Vanagreg,

Merci d'avoir répondu à ma question.
Voilà le problème : nous travaillons à plusieurs sur notre base et lors d'insertion de valeurs je voudrais que les identifiants de ces valeurs se suivent, ainsi au lieu d'avoir ça :

1. création
5. édition
6. suppression

J'aurais ça :
1. création
2. édition
3. suppression

L'objectif de la manipe est donc de réserver une plage d'id, afin que même si deux personnes insèrent des données dans la même table en même temps, nous ayons chacun des valeurs dont les identifiants se suivent.
L'enjeux est que dans nos sélections au lieu de faire ça :

Code :

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

1
2
3
4
5
6
7
8
SELECT
	a.valeur
FROM
	TA_LIBELLE a
WHERE
	UPPER(a.valeur) = UPPER('création')
	OR  UPPER(a.valeur) = UPPER('édition')
	OR UPPER(a.valeur) = UPPER('suppression');

Ce qui peut être source d'erreur puisque je fais des conditions sur du VARCHAR, je voudrais pouvoir faire ça :

Code :

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

1
2
3
4
5
6
SELECT
	a.valeur
FROM
	TA_LIBELLE a
WHERE
	a.id BETWEEN 1 AND 3;

Après que veux tu dire quand tu parles d'utiliser la gestion du cache pour réserver des ids ?

[skuatamad]

Une séquence n'est pas conçue pour ce genre de besoin.
L'objectif est de générer le plus efficacement possible en concurrence d'accès une valeur numérique généralement utilisée pour alimenter une clé primaire.

Il faut passer par une séquence "à la main" : Une fonction pipelined qui met à jour une table afin de réserver les id et les renvoyer.
PRAGMA AUTONOMOUS_TRANSACTION pour commit dans la fonction sans commit la transaction principale, ce qui implique que si la transaction principale est annulée alors les id sont perdus comme pour une séquence classique, mais qui évite de sérialiser les transactions appelant cette fonction.

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
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
drop table ma_seq;
create table ma_seq (table_name varchar2(30) primary key, val number(38) not null);
insert into ma_seq values ('T',1);
commit;
 
create or replace function reserve_seq_val (p_table_name ma_seq.table_name%type, p_nb_a_reserver ma_seq.val%type) 
return sys.odcinumberlist pipelined is
pragma autonomous_transaction;
--
l_val ma_seq.val%type;
--
begin
  --
  -- Pour tester la concurrence d'accès :
  -- dbms_lock.sleep(2);
  --
  select val into l_val
    from ma_seq 
   where table_name = p_table_name
     for update wait 3;
  --   
  update ma_seq set val = l_val + p_nb_a_reserver
   where table_name = p_table_name
  returning val into l_val;
  commit;
  --
  for i in (l_val - p_nb_a_reserver)..(l_val-1) loop
    pipe row (i);
  end loop;
  --
  exception
    when no_data_found then 
      raise_application_error(-20001,'La table '||p_table_name||' n''est pas paramétrée dans la table MA_SEQ'); 
end;
/
show err
 
 
select * from table(reserve_seq_val('T',3));
 
                           COLUMN_VALUE
---------------------------------------
                                      1
                                      2
                                      3

Après il me semble plus judicieux de conserver la PK alimentée avec la séquence et d'alimenter une nouvelle colonne avec ses valeurs réservées.

[EDIT] Bon en fait comme je commit dans la fonction juste après l'update, le SELECT INTO FOR UPDATE est inutile car il n'y a plus de risque d'attendre trop longtemps la fin de la 1ere transaction ayant appelée la fonction.

[skuatamad]

Par contre si c'est juste pour différencier des données créées par différents utilisateurs, il suffit de stocker également l'utilisateur, et une date de créations par exemple.

[vanagreg]

Bonjour,

Je pense que tu prends le problème à l'envers. Tu veux adapter ton modèle en fonction de tes requêtes, ça c'est plus du no sql.
Je suppose que la table que tu donnes en exemple n'est pas la seule, mais si par exemple c'est bien une table de libellé, alors ces libellés doivent déjà être préchargés dans une table de référentiel par leurs ids. Tu décides à l'avance que id = 1 correspond à 'création', id = 2 correspond à 'édition', et ainsi de suite.
Quand bien même tu recherches par un code chaine de caractères, si la colonne correspondante est indexée alors tu n'auras pas de problème de perf. Il vaut mieux utiliser un code technique sans accentuation pour plus de simplicité.

[Baptiste443]

Bonjour,
merci pour vos réponses !

Skuatamad, je ne connaissais pas "PRAGMA AUTONOMOUS_TRANSACTION", je vais regarder ça, merci pour la piste !

Vanagreg, je ne change pas mon modèle mais ma méthode d'insertion (ou alors je n'ai pas compris), par contre effectivement je prends peut-être le problème par le mauvais bout (je vais approfondir ton idée de précharger les valeurs dans une table de référentiel). Concernant l'écriture des données en VARCHAR en soit je suis d'accord avec toi pour l'écriture sans accentuation, mais comme j'intègre aussi des données de sources externes, je suis obligé de garder l'écriture originale, sinon je modifie la donnée. Pour la question des perf,c'est cool s'il n'y a pas de perte de performance sur un champ varchar indexé, mais j'essaie de trouver une solution à mon problème notamment pour réduire la taille de mes requêtes, ce qui signifie pas de conditions sur du varchar.

[escartefigue]

Une séquence n'est pas conçue pour ce genre de besoin.
L'objectif est de générer le plus efficacement possible en concurrence d'accès une valeur numérique généralement utilisée pour alimenter une clé primaire.

Je plussoie avec énergie et enfonce le clou : vouloir utiliser les séquences dans un but fonctionnel est une hérésie. Ces identifiants ne sont pas conçus pour ça, la distribution des valeurs est régie par le SGBD (par paquets ou unitairement, ça dépend du SGBD et de son paramétrage) et l'ordre d'insertion des valeurs n'est pas obligatoirement chronologique.

[pachot]

Bonjour,
Je ne suis pas sûr d'avoir compris le besoin. Mais si une transaction veut réserver, par exemple, moins de 10 ID is suffit d'utiliser une séquence mais de multiplier par dix. Exemple: quand la séquence retourne 42, on "réserve" 420,421,...429. Le suivant qui lit NEXTVAL il commencera à 430. Mais comme précisé par les autres, une séquence n'est éà que pour générer un numéro unique. Le fait que ce sont des numériques en séquence, c'est dû à l'implémentation, mais ne doit aps être utilisé fonctionnellement.