Les séquences Oracle

La notion de compteur ou de suite de nombres est implémentée sous Oracle à l'aide des séquences.

Une séquence est donc un objet appartenant à un schéma, générateur de nombres de 38 chiffres.

On récupère le prochain n° de séquence via la pseudocolonne NEXTVAL, et le n° courant via la pseudocolonne CURRVAL, ceci pour une session donnée.

Contrairement à la propriété de colonnes IDENTITY présente sous Microsoft SQL Server ou DB2, la séquence n'est fonctionnellement pas rattachée à la table qui l'utilise, ce qui a ses avantages et ses défauts.

• Avantage : plusieurs tables peuvent se partager une séquence (ce qui est fort pratique dans les héritages, lors du traitement d'une clé par des tables filles mutuellement exclusives).
• Désavantage : sans une certaine rigueur, on peut ne plus savoir quel compteur est utilisé dans une table donnée, mélanger les clés, etc.

La vue DBA_SEQUENCES (ou, selon votre niveau de permissions, les vues ALL_SEQUENCES et USER_SEQUENCES) répertorie toutes les séquences auxquelles vous pouvez accéder.

Dans la suite de ce document, je parlerai - par abus de langage - de la table USER_SEQUENCES. Il s'agit là aussi d'une vue système (et non d'une table), mais elle est plus accessible aux développeurs n'ayant pas d'accès sur les vues DBA_%. J'aurais aussi pu traiter ALL_SEQUENCES (liste des séquences sur lesquelles vous avez des droits), mais cela m'obligeait à ajouter la condition sur le schéma… en matière de complexité, nous serons déjà suffisamment servis.

Afin de pallier le désavantage relevé ci-dessus, je vous conseille de lier tables et séquences via une petite table personnelle qui vous sauvera à bien des occasions. La procédure qui l'accompagne a pour effet de faire le travail pour vous, et de documenter correctement votre colonne clé.

CREATE TABLE TABLES_SEQUENCES 
(
NomSchema VARCHAR2(30) NOT NULL,
NomSequence VARCHAR2(30) NOT NULL,
NomTable VARCHAR2(30),
NomColonne VARCHAR2(30),
);

CREATE PROCEDURE SP_AJOUT_SEQUENCE 
    (
    pSchema     VARCHAR2(30), 
    pSequence    VARCHAR2(30),
    pTable         VARCHAR2(30),
    pColonne     VARCHAR2(30)
     )
AS
/* Auteur : Fabien Celaia
 * Date   : 27.7.2009
 * Type   : Précédure stockée
 * Nom    : SP_AJOUTE_SEQUENCE
 * Desc . : Crée une séquence et l'associe à une colonne d'une table
 */
vSql varchar2(300) ;
BEGIN
INSERT INTO TABLES_SEQUENCES VALUES(pSchema, pSequence, pTable, pColonne) ;

vSql := 'CREATE SEQUENCE '||pSchema||'.'||pSequence ||' INCREMENT BY 1 NOCACHE ORDER NOCYCLE'
execute immediate vSql ;

vSql := 'COMMENT ON COLUMN  '||pSchema||'.'||pTable ||'.'||pColonne ||' IS ''Incrémenté par la séquence '||pSchema||'.'||pSequence ||'''' ;
execute immediate vSql ;

COMMIT ;
END ;

Le luxe est de substituer toute commande CREATE SEQUENCE par l'appel de la procédure, via un déclencheur sur DDL.

Que ce soient les scripts de certains DBAs ou la plupart des outils de rétrogénération (Toad, PowerAMC, SQL Developer…), ils utilisent les vues %_SEQUENCES afin de créer la commande de création des séquences. Ils utilisent fortuitement la colonne LAST_NUMBER de la table/vue ALL_SEQUENCES pour déterminer la valeur de redémarrage du compteur de la séquence.

Généralement, un DBA fait de même via le type de commande suivante

set pagesize 0
set linesize 150
spool RecreeSequence.sql
select 'CREATE SEQUENCE '|| sequence_owner||'.'|| sequence_name 
        ||' MINVALUE '|| to_char(min_value) 
        ||' MAXVALUE '|| to_char(max_value) 
        ||' INCREMENT BY '|| to_char(increment_by) 
        ||' START WITH '|| to_char(last_number ) 
        || case when cache_size > 0 then ' CACHE '|| cache_size else ' NOCACHE' end 
        || case order_flag when 'Y' then ' ORDER' else ' NOORDER' end 
        || case cycle_flag when 'Y' then ' CYCLE' else ' NOCYCLE' end 
        ||' ;' 
from dba_sequences 
where sequence_owner=&Schema ; 
spool off

Comme je vais le démontrer ci-dessous, ceci n'est pas un choix adéquat.

Soit une séquence créée avec les paramètres suivants :

• Nom = seq_unitaire ;
• Minimum = 1 ;
• Incrément = 1 ;
• Cache = non ;
• Cycle = non ;
• Ordre = oui.

CREATE SEQUENCE SEQ_UNITAIRE NOCACHE ORDER NOCYCLE ;

En nous référant à la table USER_SEQUENCES, nous pouvons ressortir une valeur intéressante :

SELECT  last_number 
FROM USER_SEQUENCES 
WHERE sequence_name='SEQ_UNITAIRE';

LAST_NUMBER             
---------------------- 
1                       
   
1 rows selected

Un appel à la fonction currval retourne une erreur si elle n'est pas précédée par un nextval, la structure séquence n'étant pas instanciée pour la session donnée.

select seq_unitaire.currval FROM dual ; 

Erreur commençant à la ligne 6 de la commande : 
select seq_unitaire.currval FROM dual 
Rapport d''erreur : 
Erreur SQL : ORA-08002: sequence SEQ_UNITAIRE.CURRVAL is not yet defined in this session 
08002. 00000 -  "sequence %s.CURRVAL is not yet defined in this session" 
*Cause:    sequence CURRVAL has been selected before sequence NEXTVAL 
*Action:   select NEXTVAL from the sequence before selecting CURRVAL

Nous pouvons donc en déduire que le currval ne se réfère pas au LAST_NUMBER de la table USER_SEQUENCE, mais à la séquence elle-même.

select seq_unitaire.nextval FROM dual ; 

NEXTVAL                 
---------------------- 
1                       
        
1 rows selected

Bien que l'on ait pu s'attendre à un 2, on peut comprendre qu'il retourne la valeur disponible pour le prochain insert… et c'est 1, pas 0 ! Il recherche donc bien le LAST_NUMBER de la table USER_SEQUENCES lors du premier appel.

select seq_unitaire.currval FROM dual ; 

CURRVAL                 
---------------------- 
1                       

1 rows selected

Le nextval ayant instancié ma séquence à 1, le currval est à 1… OK !

select seq_unitaire.nextval FROM dual ; 

NEXTVAL                 
---------------------- 
2     

1 rows selected

le nextval passe à 2 suivant l'incrément, ce qui est le comportement attendu.

select  last_number from USER_SEQUENCES where sequence_name='SEQ_TEST'; 

LAST_NUMBER             
---------------------- 
2         


1 rows selected 

select seq_test.currval FROM dual ; 

CURRVAL                 
---------------------- 
2                       

1 rows selected

Logique : le nextval pousse la séquence à 2 (avec incrément par défaut à 1) et le currval suit…

Il semblerait donc que l'on puisse récupérer la valeur suivante de la séquence via la vue système USER_SEQUENCES et sa colonne LAST_NUMBER.

De par son comportement, la séquence est un objet qui peut se trouver très fortement sollicité. Comment donc Oracle résout-il ce risque de conflit d'accès sur la table sous-jacente aux vues système %_SEQUENCES ?

Le cache détermine la marge que la séquence a pour s'autogérer… sans repasser par une modification de ladite table.

Prouvons donc ce comportement avec une nouvelle séquence SEQ_CACHE20… J'en profite d'ailleurs pour souligner le fait que le cache=20 est le comportement par défaut lors de la création d'une séquence sans paramètres :

• Nom = seq_cache20 ;
• Minimum = 1 ;
• Incrément = 1 ;
• Cache = 20 ;
• Cycle = non ;
• Ordre = oui.

CREATE SEQUENCE SEQ_CACHE20 ;

Le premier appel à la séquence l'instancie et fait passer le LAST_NUMBER à LAST_NUMBER+CACHE = 21

select seq_cache20.nextval from dual ;
NEXTVAL                 
---------------------- 
1                       

select  last_number from USER_SEQUENCES where sequence_name='SEQ_TEST'; 

LAST_NUMBER             
---------------------- 
21         


1 rows selected 

select seq_test.currval FROM dual ; 

CURRVAL                 
---------------------- 
2                       

1 rows selected

Nous avons donc la preuve que le LAST_NUMBER n'est pas l'équivalent de la séquence suivante (NEXTVAL) lorsque le cache existe.

Ceci est un comportement voulu par Oracle pour éviter des accès trop importants sur le métamodèle.

• Dans le cas d'un cycle normal, la séquence s'autogère tant que le nombre de nextval ne dépasse pas son cache. Lorsqu'il est atteint, une nouvelle tranche est assignée à la séquence, avec écriture dans USER_SEQUENCES.
• En cas de crash, d'arrêt de la base brutal, la nouvelle instanciation de la séquence remontera le LAST_NUMBER, quitte à « perdre » quelques séquences… mais c'est le prix de la performance à payer.

La preuve en image :

select seq_cache20.nextval, last_number 
from  USER_SEQUENCES 
where sequence_name='SEQ_CACHE20' ; 

   NEXTVAL LAST_NUMBER 
---------- ----------- 
        2          21 

SQL> / -- répété 18 fois

   NEXTVAL LAST_NUMBER 
---------- ----------- 
        20          21 

SQL> / 

   NEXTVAL  LAST_NUMBER 
----------  ----------- 
        21           21 

SQL> / 

   NEXTVAL  LAST_NUMBER 
----------  ----------- 
        22           41

Si votre application doit obtenir un n° de séquence et n'accepte pas de « trous » dans sa suite, Oracle recommande de ne pas utiliser la séquence. Il serait pourtant possible de l'utiliser en lui spécifiant les options NOCACHE, ORDER, INCREMENT BY 1, mais ceci n'est pas conseillé… sans doute pour des problèmes de conflit d'accès.

La plupart du temps, les séquences sont créées pour générer des clés techniques. Dans ce cas, l'ordre de la séquence n'est pas forcément une obligation si un gain de performance peut apparaître.

Ce paramètre est important en environnement RAC. Rappelez-vous : dans un RAC, nous avons plusieurs instances se partageant une même base de données. Une séquence en mode ORDER va totalement inhiber l'effet d'un cache puisqu'à chaque demande, le cache-fusion devra vérifier que, sur les autres instances, la valeur de la séquence n'a pas été prise.

Dans un environnement RAC, il est donc primordial – afin de ne pas trop impacter l'Interconnect – d'utiliser soit CACHE/NOORDER, soit NOCACHE/ORDER, moins performant.

Source : Best Practices for Deploying Oracle Real Application Clusters in a High Availability Environment

Noeud 1> create sequence seq_rac ;

Noeud 1> select seq_rac.currval from dual ;

Erreur commençant à la ligne 6 de la commande : 
select seq_rac.currval FROM dual 
Rapport d''erreur : 
Erreur SQL : ORA-08002: sequence SEQ_RAC.CURRVAL is not yet defined in this session 
08002. 00000 -  "sequence %s.CURRVAL is not yet defined in this session" 
*Cause:    sequence CURRVAL has been selected before sequence NEXTVAL 
*Action:   select NEXTVAL from the sequence before selecting CURRVAL 

-- Logique puisque la séquence n'est pas encore instanciée => instanciation

Noeud 1> select seq_rac.nextval from dual ;

NEXTVAL
-------
1


Noeud 1> select last_number, order_flag,  cache_size from user_sequences where sequence_name = 'SEQ_RAC' ; 

LAST_NUMBER            ORDER_FLAG CACHE_SIZE             
---------------------- ---------- ---------------------- 
21                     N          20                     

-- notez les paramètres par défaut 
-- => le ORDER est à FAUX
-- => le cache est actif


Noeud 1> select seq_rac.curval from dual ;

CURRVAL
-------
1

-- Toujours à 1

Noeud 2> select seq_rac.currval 

Erreur commençant à la ligne 6 de la commande : 
select seq_rac.currval FROM dual 
Rapport d''erreur : 
Erreur SQL : ORA-08002: sequence SEQ_RAC.CURRVAL is not yet defined in this session 
08002. 00000 -  "sequence %s.CURRVAL is not yet defined in this session" 
*Cause:    sequence CURRVAL has been selected before sequence NEXTVAL 
*Action:   select NEXTVAL from the sequence before selecting CURRVAL 

-- erreur  : l'instanciation de la séquence se fait bien au niveau de l'instance et non de la base (structure en mémoire) 

Noeud 2> select seq_rac.nextval from dual ;

NEXTVAL
-------
21 

--  effet du cache ! 

Noeud 1>  select last_number, order_flag,  cache_size from user_sequences where sequence_name = 'SEQ_RAC'  ;

LAST_NUMBER            ORDER_FLAG CACHE_SIZE             
---------------------- ---------- ---------------------- 
41                     N          20                     

Noeud 1> select seq_rac.curval from dual ;

CURRVAL
-------
1 

Noeud 1> select seq_rac.nextval from dual ;

NEXTVAL
-------
2

En cache/order, il n'est pas difficile d'envisager le comportement d'une séquence qui s'incrémenterait en RAC : à chaque changement de nœud, elle devra contrôler les structures en mémoire de tous les nœuds, d'où une suractivité dans le cache fusion… ce qui ne se traduit pas, dans mes tests ci-dessous, par des temps de réponse trop différents… mais j'ai un « gros » interconnect…

Noeud 1> create sequence seq_rac_order order ;

Noeud 1> select seq_rac_order.nextval from dual ;

NEXTVAL
-------
1


Noeud 1> select last_number, order_flag,  cache_size from user_sequences where sequence_name = 'SEQ_RAC_ORDER' ;

LAST_NUMBER            ORDER_FLAG CACHE_SIZE             
---------------------- ---------- ---------------------- 
21                     Y          20                     

Noeud 1> select seq_rac_order.nextval from dual ;

NEXTVAL
-------
2

Noeud 2> select seq_rac_order.nextval from dual ;

NEXTVAL
-------
3

Soit l'insertion de 100 000 lignes utilisant une séquence.

L'effet bénéfique du cache sur les performances est donc clairement prouvé.

Cette option permet de « boucler » sur certaines valeurs. Il n'est là plus question de compteur, mais d'une construction pouvant se révéler bien pratique pour générer des suites.

            CREATE SEQUENCE SEQ_CYCLE INCREMENT BY 3 START WITH 99 MAXVALUE 300 MINVALUE 99 CYCLE NOCACHE ORDER;

Il n'y a pas de commande ALTER SEQUENCE SEQ_TEST MODIFY LAST_NUMBER=1000. Afin d'incrémenter la séquence, il y a trois options, dont certaines sont plus élégantes que les autres :

1. Exécuter des nextval successifs ;
2. Supprimer, puis recréer la séquence ;
3. Modifier l'incrément.

Les nextval successifs sont possibles et simples… mais peu élégants et injouables sur de très gros intervalles.

Supprimer puis recréer la séquence avec une min_value égale à la valeur souhaitée est faisable, mais risqué puisque, à un moment donné, la séquence n'existe plus, et qu'il y a un risque d'invalider des objets dépendants, ou d'oublier de recréer un droit spécifique… Modifier la min_value risque d'avoir une influence non négligeable sur une séquence en mode CYCLE.

Reste la méthode 3, la plus élégante, faisant fi des risques de droits manquants et d'invalidation d'objets. De plus, pas de problème de performances sur les gros intervalles.

declare nseq number, nincr number ;
SELECT SEQ_MaSequence.nextval, increment  INTO nseq, nincr FROM USER_SEQUENCES WHERE SEQUENCE_NAME='SEQ_MASEQUENCE';
ALTER SEQUENCE SEQ_MASEQUENCE INCREMENT BY &NouvelleValeur - nseq ;
SELECT SEQ_MaSequence.nextval from dual ;
ALTER SEQUENCE SEQ_MASEQUENCE INCREMENT BY nincr ;

• Oracle Database Concepts
• Oracle Database SQL Language Reference

Oracle a encore frappé : d'un objet qui nous semblait anodin,nous découvrons des paramètres, des valeurs par défaut et des effets indésirables insoupçonnés.

… tout ce qui fait la richesse et la légende de l'éditeur rouge…

J'espère que ces commentaires vous seront bénéfiques et vous permettront d'éviter quelques écueils…

Un chaleureux merci à Pomalaix pour sa relecture et ses remarques constructives avisées…