Navigation

Inscrivez-vous gratuitement
pour pouvoir participer, suivre les réponses en temps réel, voter pour les messages, poser vos propres questions et recevoir la newsletter

PL/SQL Oracle Discussion :

Comment réserver ses ids ?


Sujet :

PL/SQL Oracle

  1. #1
    Futur Membre du Club
    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 :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

  2. #2
    Membre éclairé
    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?

  3. #3
    Futur Membre du Club
    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 ?

  4. #4
    Expert confirmé
    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.

  5. #5
    Expert confirmé
    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.

  6. #6
    Membre éclairé
    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é.

  7. #7
    Futur Membre du Club
    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.

  8. #8
    Futur Membre du Club
    Euh... non en fait tu as raison Vanagreg cela revient effectivement à modifier la structure de ma table d'import (TEMP_IMPORT) puisque je peux rajouter un champ et l'incrémenter à partir de la première valeur de la plage d'ids que j'ai réservée dans la table TEMP_LIBELLE. De plus ce champ s'incrémente automatiquement si j'utilise le type identity. Et ensuite il me suffira d'insérer les données dans TEMP_LIBELLE sans faire de boucle.
    C'est une solution, maintenant est-ce une bonne solution ? La question reste en suspend pour moi, en tous cas pour l'instant.
    Je modifie mon code et je vous le propose.

  9. #9
    Futur Membre du Club
    Voici une solution qui pré-suppose que la table d'import dispose du champ en_base (ce qui n'est pas un problème, puisqu'il s'agit juste d'insérer un champ vide de type NUMBER(1,0) dans la table à insérer (shp ou csv)). De plus cette solution créé et incrémente un nouveau champ dans la table d'import qui correspond aux ids que j'ai réservés.

    Je passe ainsi à une méthode en deux étapes :
    1. Créer et réserver une plage d'id ;
    2. Insérer les données ;

    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
    -- Etape 1 : Réservation des ids ;
    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;
    -- 1. Identification des libellés présents en base ou non
    UPDATE TEMP_IMPORT a
        SET a.EN_BASE = CASE
                            WHEN a.VALEUR IN(SELECT b.VALEUR FROM 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;
     
    -- 2. Suppression des valeurs déjà présentes en base
    DELETE FROM TEMP_IMPORT WHERE EN_BASE = 1;
    COMMIT;
     
    -- 3. Décompte du nombre d'objet/de valeurs à insérer en base
    SELECT
        COUNT(OGR_FID)
        INTO v_nb_id_reserve
    FROM
        TEMP_IMPORT
    WHERE
        EN_BASE = 0;
     
    -- 4. 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';
     
    -- 5. Sélection de la prochaine valeur d'incrémentation de la PK de la table d'insertion
     SELECT
        LAST_NUMBER
        INTO v_id_max
    FROM
        ALL_SEQUENCES
    WHERE
        SEQUENCE_NAME = v_nom_sequence;
     
    -- 6. Création des ids à insérer dans la table d'insertion
    EXECUTE IMMEDIATE 'ALTER TABLE TEMP_IMPORT ADD fid_objectid NUMBER(38,0) GENERATED BY DEFAULT AS IDENTITY (START WITH ' || v_id_max || ' CACHE 20)';
     
    -- 7. 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;
     
    -- 8. Modification de la valeur de départ de la séquence d'auto-incrémentation via du SQL dynamique
    EXECUTE IMMEDIATE 'ALTER TABLE TEMP_LIBELLE MODIFY OBJECTID GENERATED BY DEFAULT AS IDENTITY (START WITH ' || v_id_reserve || ')';
     
    -- 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;
     
    -- Etape 2 : Insertion des valeurs ;
    INSERT INTO TEMP_LIBELLE(objectid, valeur)
    SELECT
        fid_objectid,
        valeur
    FROM
        TEMP_IMPORT;
    COMMIT;


    Je m'aperçois aussi en vous proposant ce code que les parties 1 et 2 de l'étape 1 peuvent ne faire qu'une seule et même partie. Ce n'est pas comme si j'avais vraiment besoin du champ en_base finalement.

  10. #10
    Expert confirmé
    Je n'arrive pas à comprendre le problème que vous rencontrez mais bidouiller la séquence comme vous essayez de le faire me semble être une très mauvaise idée, vous allez au devant de nombreux problèmes.

    Si vous ne voulez pas que la séquence soit appelée par 2 sessions en même temps, vous pouvez par exemple sérialiser le chargement de la table en utilisant dbms_lock puis vous faites un INSERT SELECT WHERE NOT EXISTS (pas besoin du flag).

  11. #11
    Expert éminent sénior
    Citation Envoyé par skuatamad Voir le message
    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.

  12. #12
    Expert éminent
    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.
    Franck Pachot - dbi services - Consulting et Formation en Suisse et remote - fpa@dbi-services.com
    🗣 twitter: @FranckPachot - 📝 blog: blog.pachot.net - 🎧 podcast en français : https://anchor.fm/franckpachot

###raw>template_hook.ano_emploi###