Comment réserver ses ids ?
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:
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
