Developpez.com

Une très vaste base de connaissances en informatique avec
plus de 100 FAQ et 10 000 réponses à vos questions

Developpez.com - Oracle
X

Choisissez d'abord la catégorieensuite la rubrique :


Les séquences Oracle

Débutant

Date de publication : 23 juillet 2009 , Date de mise à jour : 28 juillet 2009

Par fadace (Fabien Celaia)
 

En chantier ! Explication du comportement des séquences Oracle et de leurs diverses options.

               Version PDF (Miroir)   Version hors-ligne (Miroir)

I. Introduction
II. Les vues système
III. Bonnes pratiques
IV. Mauvaises pratiques
V. Comportement attendu d'une séquence
VI. L'effet CACHE
VII. L'option ORDER
VIII. Performances
IX. L'option CYCLE
X. Modifier le nextval d'une séquence
XI. Documentation
XII. Conclusion
XIII. Rermerciements


I. Introduction

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ées.

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:


II. Les vues système

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.


III. Bonnes pratiques

Afin de palier 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éé 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.


IV. Mauvaises pratiques

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.


V. Comportement attendu d'une séquence

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

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.


VI. L'effet CACHE

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

CREATE SEQUENCE SEQ_CACHE20 ;
warning Attention : la taille du cache doit bien évidemment être compatible avec les option max_value et min_value.
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.

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 

VII. L'option ORDER

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.

info Par défaut, les séquences Oracle sont créées avec un cache de 20, et en mode NOORDER.
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.

Exemple en RAC


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'instantiation 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 noeud, elle devra contrôler les structures en mémoire de tous les noeuds, 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

VIII. Performances

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

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


IX. L'option CYCLE

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.
Exemple d'une séquence bouclant sur des valeurs multiples de 3, comprises entre 99 et 300

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

X. Modifier le nextval d'une séquence

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 3 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 et 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 ;

XI. Documentation


XII. Conclusion

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...


XIII. Rermerciements

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



               Version PDF (Miroir)   Version hors-ligne (Miroir)

Valid XHTML 1.0 TransitionalValid CSS!

Copyright © 2009 fadace. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents, images, etc. sans l'autorisation expresse de l'auteur. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.

Contacter le responsable de la rubrique Oracle