IdentifiantMot de passe
Mot de passe oublié ?Je m'inscris ! (gratuit)
logo

FAQ OracleConsultez toutes les FAQ

Nombre d'auteurs : 17, nombre de questions : 139, dernière mise à jour : 30 mai 2016  Ajouter une question

 

Cette F.A.Q. a été réalisée à partir des questions fréquemment posées sur le forum Oracle de www.developpez.com et de l'expérience personnelle des auteurs. Elle pourra traiter de tout type de questions portant sur les technologies Oracle.

Nous espérons que cette F.A.Q. saura répondre à un maximum de vos questions. Nous vous souhaitons une bonne lecture.

L'équipe Oracle de Developpez.

Sommaire →Développement →SQL (19)
précédent sommaire suivant
 
HautPage
Comment filtrer les n premières lignes d'une requête avec ROWNUM ?

  • Obtenir les 10 premières lignes d'une requête non triée :

Code sql : Sélectionner tout 
1
2
3
SELECT *  
  FROM EMP  
 WHERE ROWNUM <= 10 ;
  • Obtenir les 10 premières lignes d'une requête triée :

Code sql : Sélectionner tout 
1
2
3
4
5
SELECT *  
  FROM (SELECT *  
          FROM EMP  
         ORDER BY ename)  
 WHERE ROWNUM <= 10 ;
Explication de la seconde requête :

Chaque ligne retournée par une requête se voit attribuer un numéro, commençant par 1.
Cela est parfait tant que les données ne sont pas triées avec le mot-clé ORDER BY !

Par contre, si vous souhaitez trier les données, le numéro (ROWNUM) est attribué pour chaque ligne AVANT le tri final !
Prenons un exemple en affichant le contenu de la table EMP trié par salaires :

Code sql : Sélectionner tout 
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
  1  SELECT ROWNUM, a.*  
  2    FROM emp a  
  3   WHERE sal > 1000  
  4*  ORDER BY sal  
SQL>  
SQL> /  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
        11       7900 JAMES      CLERK           7698 04/12/81       1045                    30  
        10       7876 ADAMS      CLERK           7788 24/05/87       1210                    20  
        14       9991 Dupontont  CLERK           7698 24/01/82       1210                    10  
        15       9992 Duboudin   CLERK           7698 24/01/82       1215       1250         10  
         2       7521 WARD       SALESMAN        7698 23/02/81       1375        500         30  
         4       7654 MARTIN     SALESMAN        7698 29/09/81       1375       1400         30  
        13       7934 MILLER     CLERK           7782 24/01/82       1430                    10  
         9       7844 TURNER     SALESMAN        7698 09/09/81       1650          0         30  
         1       7499 ALLEN      SALESMAN        7698 21/02/81       1936        300         30  
        16       9994 Schmoll    CLERK           7698 24/05/87       2500                    20  
         6       7782 CLARK      MANAGER         7839 10/06/81       2695       2000         10  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
         5       7698 BLAKE      MANAGER         7839 02/05/81       3135                    30  
         3       7566 JONES      MANAGER         7839 03/04/81       3273                    20  
         7       7788 SCOTT      ANALYST         7566 20/04/87       3300                    20  
        12       7902 FORD       ANALYST         7566 04/12/81       3300                    20  
         8       7839 KING       PRESIDENT            18/11/81       5500                    10  
  
16 ligne(s) sélectionnée(s).
Nous ne voulons maintenant afficher que les quatre premières lignes :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
SQL> SELECT ROWNUM, a.*  
  2    FROM emp a  
  3   WHERE sal > 1000  
  4     AND ROWNUM < 5  
  5  ORDER BY sal ;  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
         2       7521 WARD       SALESMAN        7698 23/02/81       1375        500         30  
         4       7654 MARTIN     SALESMAN        7698 29/09/81       1375       1400         30  
         1       7499 ALLEN      SALESMAN        7698 21/02/81       1936        300         30  
         3       7566 JONES      MANAGER         7839 03/04/81       3273                    20
les CLERK ont disparu !

Simplement parce que le tri a été effectué après que le ROWNUM ait été affecté aux lignes retournées par la requête.

Il faut donc trier les données avant qu'Oracle ne leur affecte un numéro !
Ceci est réalisé par un tri inclus dans une sous-requête :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
SQL> SELECT  ROWNUM, a.*  
  2  FROM  (  
  3      SELECT *  
  4      FROM  emp  
  5      ORDER BY sal  
  6     ) a  
  7  WHERE  ROWNUM < 5 ;  
  
    ROWNUM      EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO  
---------- ---------- ---------- --------- ---------- -------- ---------- ---------- ----------  
         1       7369 SMITH      CLERK           7902 18/12/80        880                    20  
         2       7900 JAMES      CLERK           7698 04/12/81       1045                    30  
         3       7876 ADAMS      CLERK           7788 24/05/87       1210                    20  
         4       9991 Dupontont  CLERK           7698 24/01/82       1210                    10
Voici enfin le résultat attendu.

Mis à jour le 30 novembre 2004 SheikYerbouti
HautPage
Comment sélectionner du m-ième au n-ième enregistrements ?

Voici une méthode pour filtrer la liste des enregistrements sur des intervalles consécutifs depuis la version 8i :

  • les cinq premiers :

Code sql : Sélectionner tout 
1
2
3
4
5
SELECT ename, job 
  FROM (SELECT ename, job, ROW_NUMBER() OVER (ORDER BY sal) num 
          FROM emp  
       )  
 WHERE num BETWEEN 1 AND 5;
  • les cinq suivants :

Code sql : Sélectionner tout 
1
2
3
4
5
SELECT ename, job 
  FROM (SELECT ename, job, ROW_NUMBER() OVER (ORDER BY sal) num 
          FROM emp  
       )  
 WHERE num BETWEEN 6 AND 10;
  • de la m-ième à la n-ième ligne :

Code sql : Sélectionner tout 
1
2
3
4
5
SELECT ename, job 
  FROM (SELECT ename, job, ROW_NUMBER() OVER (ORDER BY sal) num 
          FROM emp  
       )  
 WHERE num BETWEEN &m AND &n;
Ces exemples utilisent la fonction analytique ROW_NUMBER. Cette fonction est très similaire au ROWNUM utilisé dans le paragraphe précédent mais est appliqué sur le résultat de la requête et pas pendant l'exécution de la requête elle-même.
Avec ROWNUM, la requête précédente aurait pu s'écrire :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
SELECT ename, job 
  FROM ( SELECT ename, job, ROWNUM num  
           FROM ( 
		         SELECT ename, job 
                   FROM emp ORDER BY sal 
				) 
       )  
 WHERE num BETWEEN &m AND &n;
Les fonctions analytiques peuvent sensiblement simplifier les requêtes et éventuellement améliorer leurs performances.

Mis à jour le 30 novembre 2004 orafrance
HautPage
Comment détecter et supprimer les doublons ?

Supposons qu'on ait une table PERSONNE (nom, prenom, telephone) et qu'on souhaite avoir la liste des personnes ayant le même nom et le même prénom. On peut l'obtenir avec la requête suivante :

Code sql : Sélectionner tout 
1
2
3
4
select nom, prenom 
from PERSONNE 
group by nom, prenom 
having count(*) > 1;
Si on ne veut garder qu'une seule ligne pour chaque ensemble de personnes ayant le même nom et le même prénom, le choix se faisant de manière arbitraire, on peut utiliser la requête suivante :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
delete PERSONNE a 
where rowid > ( 
select min(rowid) 
from PERSONNE b 
where b.nom = a.nom and 
b.prenom = a.prenom 
);
Remarque :
  • cette requête doit être adaptée si l'une des colonnes nom ou prénom peut être nulle.
  • cette requête garde la ligne de rowid minimum parmi l'ensemble des lignes ayant même nom et même prénom.

Mis à jour le 18 septembre 2006 lalystar
Comment sélectionner du m-ième au n-ième enregistrements ?
HautPage
Comment détecter les enregistrements empêchant la validation d'une contrainte ?

À l'aide de la table des exceptions : elle peut se créer via ce script :

Code sql : Sélectionner tout 
1
2
3
4
5
6
create table exceptions( 
    row_id rowid,  
    owner varchar2(30),  
    table_name varchar2(30),  
    constraint varchar2(30) 
);

Ou en utilisant le script fournit dans $ORACLE_HOME/RDBMS/admin/UTLEXCPT.SQL.

Voyons son utilisation au travers de ce jeu d'essais :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
create table test ( 
    a varchar2(10), 
    b number 
); 
  
-- Insertion des valeurs 
insert into test values ('a',1); 
insert into test values ('b',2); 
insert into test values ('c',3); 
insert into test values ('d',4); 
insert into test values ('a',5); 
insert into test values ('e',6); 
insert into test values ('f',7); 
insert into test values ('b',8); 
insert into test values ('a',9); 
insert into test values ('z',10); 
commit;

Essayons maintenant de créer une contrainte d'unicité : nous allons obtenir une erreur :

Code sql : Sélectionner tout 
1
2
3
4
5
6
SQL> alter table test add constraint TEST_PK unique  (a)  
  2  exceptions into exceptions ; 
alter table test add constraint TEST_PK unique  (a) 
                                * 
ERREUR à la ligne 1 : 
ORA-02299: impossible de valider (SCOTT.TEST_PK) - clés en double trouvées

La requête suivante nous permet d'identifier les lignes posant problème :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
SQL> select * from test where rowid in ( select row_id from exceptions where table_name='TEST' and owner ='SCOTT' ) ; 
  
A                   B 
---------- ---------- 
a                   1 
b                   2 
a                   5 
b                   8 
a                   9

Mis à jour le 18 septembre 2006 Jaouad
HautPage
Comment supprimer/remplacer des caractères avec la fonction TRANSLATE ?

Cette fonction permet d'effectuer plusieurs remplacements d'un coup (à l'inverse de REPLACE() qui ne permet le remplacement que d'un caractère ou une chaîne à la fois) :

TRANSLATE( chaîne en entrée, chaîne de recherche, chaîne de substitution ) ;

  • Exemple de suppression de caractères indésirables pour la constitution d'un nom de fichier :

Code sql : Sélectionner tout 
1
2
3
4
5
6
SQL>  SELECT TRANSLATE ( 'Nom/de~fichier;non.conforme', '1/\^~.,;', '1' ) "Fichier"  
  2   FROM DUAL ;  
  
Fichier  
-----------------------  
Nomdefichiernonconforme
Le premier argument de la fonction est la chaîne en entrée,
Le deuxième indique la chaîne de recherche,
Le troisième indique la substitution caractère par caractère,

En clair, le premier caractère de la chaîne de recherche trouvé dans la chaîne en entrée sera remplacé par le premier caractère de la chaîne de substitution et ainsi de suite pour les autres caractères.
La ruse dans l'exemple est le premier caractère '1' dans les deux derniers arguments. On dit qu'il s'agit d'un caractère leurre, pusiqu'il sert à ignorer tous les caractères de la chaîne en entrée qui ne sont pas dans la chaîne de recherche.
La fonction remplace tous les '1' par '1' et tous les autres caractères de la chaîne de recherche trouvés dans la chaîne en entrée par rien !

  • Exemple de remplacement de caractères indésirables pour la constitution d'un nom de fichier :

Code sql : Sélectionner tout 
1
2
3
4
5
6
SQL> SELECT TRANSLATE ( 'Nom/de~fichier;non.conforme', '1/\^~.,;', '1_______' ) "Fichier"  
  2  FROM DUAL ;  
  
Fichier  
---------------------------  
Nom_de_fichier_non_conforme
La fonction remplace tous les '1' par '1' et tous les autres caractères de la chaîne de recherche par le caractère '_'.

  • Autre exemple de remplacement de caractères indésirables :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
SQL> SELECT TRANSLATE ( 'àâäéèëêùûüô', 'àâäéèëêùûüô', 'aaaeeeeuuuo' ) "Chaine"  
  2  FROM DUAL ;  
  
Chaine  
-----------  
aaaeeeeuuuo  
  
SQL>
La fonction remplace toutes les minuscules accentuées par leurs équivalents non accentués.

Bien évidement, les arguments de la fonction peuvent être des variables :

Code sql : Sélectionner tout 
1
2
3
4
FUNCTION Remplace ( PC$Chaine IN VARCHAR2, PC$Rech IN VARCHAR2 , PC$Subst IN VARCHAR2 ) RETURN VARCHAR2 IS  
BEGIN  
   RETURN TRANSLATE( PC$Chaine, PC$Rech, PC$Subst ) ;  
END ;

Mis à jour le 30 novembre 2004 SheikYerbouti
HautPage
Comment manipuler la partie entière et décimale d'un numérique avec la fonction TRUNC ?

TRUNC( valeur, précision ) ;
Cette fonction permet d'effectuer deux types d'action (sur un numérique) :

  • Conserver le nombre de décimales souhaitées (précision >= 0)
  • Cadrer la partie entière d'un nombre dans une tranche (précision < 0)

  • Exemple de conservation des décimales voulues :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
SQL> select  
  2   trunc(12.98764, 5) "+5 dec."  
  3  ,trunc(12.98764, 4) "+4 dec."  
  4  ,trunc(12.98764, 3) "+3 dec."  
  5  ,trunc(12.98764, 2) "+2 dec."  
  6  ,trunc(12.98764, 1) "+1 dec."  
  7  ,trunc(12.98764, 0) "+0 dec."  
  8   from dual ;  
  
   +5 dec.    +4 dec.    +3 dec.    +2 dec.    +1 dec.    +0 dec.  
---------- ---------- ---------- ---------- ---------- ----------  
  12,98764    12,9876     12,987      12,98       12,9         12
  • Exemple de cadrage par tranches :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
SQL> select  
  2   trunc(1234567,-6) "million"  
  3  ,trunc(1234567,-5) "cent-mille"  
  4  ,trunc(1234567,-4) "dix-mille"  
  5  ,trunc(1234567,-3) "mille"  
  6  ,trunc(1234567,-2) "cent"  
  7  ,trunc(1234567,-1) "dix"  
  8  from dual ;  
  
   million cent-mille  dix-mille      mille       cent        dix  
---------- ---------- ---------- ---------- ---------- ----------  
   1000000    1200000    1230000    1234000    1234500    1234560
  • Extraction des parties entière et décimale d'un nombre :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
SQL> DECLARE  
  2    LN$Num number := 100.95 ;  
  3  BEGIN  
  4    DBMS_OUTPUT.PUT_LINE( 'Partie entière  : ' || To_char( TRUNC ( LN$Num ) )) ;  
  5    DBMS_OUTPUT.PUT_LINE( 'Partie décimale : ' || To_char( LN$Num - TRUNC (LN$Num ) ) ) ;  
  6  END ;  
  7  /  
Partie entière  : 100  
Partie décimale : ,95  
  
Procédure PL/SQL terminée avec succès.

Mis à jour le 30 novembre 2004 SheikYerbouti
HautPage
Comment tronquer une date avec la fonction TRUNC ?

La fonction TRUNC() appliquée à une date ne permet pas seulement l'ablation de la partie horaire.

Nous connaissons tous cette fonction qui, sans spécification de son deuxième argument, retire la partie horaire.
Mais nous connaissons souvent moins l'autre particularité de cette fonction appliquée aux dates, par l'intermédiaire du second argument (format).

TRUNC( date [,'format'] )

Code sql : Sélectionner tout 
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
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
SQL> -- Format étendu d'affichage des dates  --  
SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'DD/MM/YYYY HH24:MI:SS' ;  
  
Session modifiée.  
  
SQL>  
SQL> -- Affichage de la date du jour --  
SQL> SELECT SYSDATE "Date du jour" FROM DUAL ;  
  
Date du jour                                                                     
-------------------                                                               
08/10/2004 14:08:48                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour de l'année --  
SQL> SELECT TRUNC(SYSDATE, 'YEAR') "'YEAR'" FROM DUAL;  
  
'YEAR'                                                                           
-------------------                                                               
01/01/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour de l'année ISO--  
SQL> SELECT TRUNC(SYSDATE, 'IYYY') "'IYYY'" FROM DUAL;  
  
'IYYY'                                                                           
-------------------                                                               
29/12/2003 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour du trimestre --  
SQL> SELECT TRUNC(SYSDATE, 'Q') "'Q'" FROM DUAL;  
  
'Q'                                                                               
-------------------                                                               
01/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour du mois --  
SQL> SELECT TRUNC(SYSDATE, 'MONTH') "'MONTH'" FROM DUAL;  
  
'MONTH'                                                                           
-------------------                                                               
01/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au 1er jour de la semaine --  
SQL> SELECT TRUNC(SYSDATE, 'DAY') "'DAY'" FROM DUAL;  
  
'DAY'                                                                             
-------------------                                                               
04/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- jour de la semaine correspondant au jour du 1er jour du mois --  
SQL> SELECT TRUNC(SYSDATE, 'W')  "'W'" FROM DUAL;  
  
'W'                                                                               
-------------------                                                               
08/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- jour de la semaine correspondant au jour du 1er jour de l'année --  
SQL> SELECT TRUNC(SYSDATE, 'WW')  "'WW'" FROM DUAL;  
  
'WW'                                                                             
-------------------                                                               
07/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- jour de la semaine correspondant au jour du 1er jour de l'année ISO --  
SQL> SELECT TRUNC(SYSDATE, 'IW')  "'IW'" FROM DUAL;  
  
'IW'                                                                             
-------------------                                                               
04/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée au jour (retire les heures) --  
SQL> SELECT TRUNC(SYSDATE, 'DD') "'DD'" FROM DUAL;  
  
'DD'                                                                             
-------------------                                                               
08/10/2004 00:00:00                                                               
  
SQL>  
SQL> -- date tronquée à l'heure (retire les minutes) --  
SQL> SELECT TRUNC(SYSDATE, 'HH') "'HH'" FROM DUAL;  
  
'HH'                                                                             
-------------------                                                               
08/10/2004 14:00:00                                                               
  
SQL>  
SQL> -- date tronquée à la minute (retire les secondes) --  
SQL> SELECT TRUNC(SYSDATE, 'MI') "'MI'" FROM DUAL;  
  
'MI'                                                                             
-------------------                                                               
08/10/2004 14:08:00

Mis à jour le 30 novembre 2004 SheikYerbouti
HautPage
Comment formater les dates ?

Il existe beaucoup de formats de date disponibles avec Oracle.

Voici une liste non exhaustive des différents formats de date disponible.

  • - / , . ; : "text" : Ponctuation ou texte à insérer dans la chaîne finale.
  • AD A.D. : Pour ajouter les sigles après Jésus Christ
  • AM A.M. : Symbole Am
  • BC B.C. : Pour ajouter les sigles avant Jésus Christ
  • CC SCC : Pour obtenir le n° du siècle en cours
  • D : N° du jour dans la semaine (par exemple Lundi =1, Mardi =2, etc.)
    Attention la notation peut changer en fonction de vos paramètres NLS.
  • DAY : Nom du jour (compensé avec des espaces jusqu'à 9 caractères)
  • DD : Le numéro du jour dans le mois (de 1 à 31)
  • DDD : Le numéro du jour dans l'année (de 1 à 366)
  • DY: Abréviation du nom du jour
  • E : Abréviation du nom de l'ère en cours (pour les calendriers Japonais, ROC et Thai).
  • EE : Nom complet de l'ère en cours
  • FM : Permet de supprimer les espaces inutiles
  • HH: Heure (de 1 à 12)
  • HH12: Heure (de 1 à 12)
  • HH24: Heure (de 1 à 23)
  • IW: Numéro de la semaine dans l'année (de 1 à 52 ou 53 en fonction de l'année)
  • IYY IY I : Les 3, 2 ou 1 derniers chiffres de l'année
  • IYYY : Les 4 chiffres de l'année
  • J: Nombre de jours depuis la date January 1, 4712 BC
  • MI: Minutes (de 1 à 59)
  • MM: Numéro du mois dans l'année
  • MON : Abréviation du nom du mois
  • MONTH : Nom du mois (compensé avec des espaces jusqu'à 9 caractères)
  • PM P.M. : Symbole Pm
  • Q: Numéro du trimestre
  • RM: Numéro du mois au format romain
  • RR: Les deux derniers chiffres de l'année (avec prise en compte du siècle en cours)
  • RRRR: Les 4 derniers chiffres de l'année (avec prise en compte du siècle en cours)
  • SS: Nombre de secondes (de 0 à 59)
  • SSSSS: Nombre de secondes écoulées depuis minuit (de 0 à 86399)
  • W: Numéro de la semaine dans le mois (de 1 à 5)
  • Y,YYY: Numéro de l'année avec une virgule pour délimiter le millénaire (par exemple 2,004)
  • YEAR : Épellation du numéro de l'année en cours
  • YYYY: Numéro de l'année en cours (sans prise en compte du siècle en cours)
  • YYY YY Y : Les 3, 2 ou 1 derniers chiffres de l'année

Exemple :

Code sql : Sélectionner tout 
1
2
3
4
5
6
SELECT to_char(sysdate,'DD/MM/RRRR Day D Month Year') 
FROM dual; 
  
TO_CHAR(SYSDATE,'DD/MM/RRRRDAYDMONTHYEAR')   
------------------------------------------------------------------------------------ 
15/10/2004 Vendredi 5 Octobre Two Thousand Four

Mis à jour le 30 novembre 2004 helyos
HautPage
Quelles sont les limitations d'une contrainte CHECK ?

Les contraintes CHECK sont pratiques pour mettre en place un simple contrôle statique de validité des données, mais elles sont très limitées. Il faudra alors recourir à des déclencheurs pour des validations plus complexes.

Une contrainte CHECK ne peut faire référence qu'aux colonnes de la ligne courante.
Elle ne peut donc pas impliquer d'autres lignes de la table, ni impliquer d'autres tables.
En particulier, on ne peut pas y utiliser un SELECT.

Dans une contrainte CHECK, on ne peut pas faire appel à une fonction personnalisée.
Les fonctions standard (comme LENGTH ou UPPER par exemple) sont autorisées, à condition qu'elles soient déterministes.
C'est pourquoi on ne peut pas utiliser les fonctions SYSDATE ou USER, qui provoquent l'une comme l'autre l'erreur "ORA-02436: variable de date ou système mal indiquée dans contrainte CHECK".

Pour résumer, une contrainte CHECK ne peut mettre en jeu que les colonnes de la ligne courante, des constantes éventuellement sous forme de listes, et des fonctions standard déterministes.

Exemples :

Code sql : Sélectionner tout 
1
2
CHECK (UPPER(titre) IN ('M.', 'MME', 'MLLE')) 
CHECK ((col1 < col2) OR (col1 = 10))

Mis à jour le 30 novembre 2004 Pomalaix
HautPage
Quelle est la différence entre les types CHAR et VARCHAR2 ?

Il existe essentiellement une différence de place entre ces types de données . En effet, VARCHAR2 a une longueur variable qui dépend directement de la taille de la donnée insérée alors que CHAR à une longueur fixe (défini à la création de la table).

Prenons l'exemple d'un VARCHAR2 (20) et CHAR(20), si on insère 'Oracle' dans les deux colonnes, Oracle sera complété par des espaces dans la colonne CHAR(20) :

Code sql : Sélectionner tout 
1
2
3
create table dvp ( a char(20), b varchar2(20)) ;  
insert into dvp values ('oracle','oracle') ;  
commit ;
Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
SQL> select '-'||a||'-', '-'||b||'-' from dvp ;  
  
'-'||A||'-'            '-'||B||'-' 
---------------------- ---------------------- 
-oracle              - -oracle-  
  
SQL> select length(a) , length(b) from dvp ;  
  
 LENGTH(A)  LENGTH(B)  
---------- ----------  
        20          6
La Longueur maximale de Char (2000) et Varchar2 (4000) sur une 8i :

Code sql : Sélectionner tout 
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
SQL> create table dvp ( a varchar2(4001) ;  
create table dvp ( a varchar2(4001)  
                              *  
ERREUR à la ligne 1 :  
ORA-00910: specified length too long for its datatype  
  
  
SQL> create table dvp ( a varchar2(4000)) ;  
  
Table créée.  
  
SQL>  drop table dvp ;  
  
Table supprimée.  
  
SQL>  create table dvp ( a char(2001)) ;  
 create table dvp ( a char(2001))  
                           *  
ERREUR à la ligne 1 :  
ORA-00910: specified length too long for its datatype  
  
  
SQL> create table dvp ( a char(2000)) ;  
  
Table créée.
Oracle recommande quand même l'utilisation de VARCHAR2 au lieu de CHAR pour une raison de compatibilité dans les versions supérieures.

Outre l'utilisation supplémentaire de place, l'utilisation de CHAR en lieu et place de VARCHAR2 augmentera également la taille de vos index et peut parfois diminuer les performances de recherches de ces derniers !

Mis à jour le 18 septembre 2006 Jaouad
HautPage
Comment détecter les verrous (locks) ?

Via la requête suivante :

Code sql : Sélectionner tout 
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
75
76
77
SELECT   /*+ choose */  
         bs.username "Blocking User",  
         bs.username "DB User",  
         ws.username "Waiting User",  
         bs.sid "SID",  
         ws.sid "WSID",  
         bs.sql_address "address",  
         bs.sql_hash_value "Sql hash",  
         bs.program "Blocking App",  
         ws.program "Waiting App",  
         bs.machine "Blocking Machine",  
         ws.machine "Waiting Machine",  
         bs.osuser "Blocking OS User",  
         ws.osuser "Waiting OS User",  
         bs.serial# "Serial#",  
         DECODE (  
            wk.TYPE,  
            'MR', 'Media Recovery',  
            'RT', 'Redo Thread',  
            'UN', 'USER Name',  
            'TX', 'Transaction',  
            'TM', 'DML',  
            'UL', 'PL/SQL USER LOCK',  
            'DX', 'Distributed Xaction',  
            'CF', 'Control FILE',  
            'IS', 'Instance State',  
            'FS', 'FILE SET',  
            'IR', 'Instance Recovery',  
            'ST', 'Disk SPACE Transaction',  
            'TS', 'Temp Segment',  
            'IV', 'Library Cache Invalidation',  
            'LS', 'LOG START OR Switch',  
            'RW', 'ROW Wait',  
            'SQ', 'Sequence Number',  
            'TE', 'Extend TABLE',  
            'TT', 'Temp TABLE',  
            wk.TYPE  
         ) lock_type,  
         DECODE (  
            hk.lmode,  
            0, 'None',  
            1, 'NULL',  
            2, 'ROW-S (SS)',  
            3, 'ROW-X (SX)',  
            4, 'SHARE',  
            5, 'S/ROW-X (SSX)',  
            6, 'EXCLUSIVE',  
            TO_CHAR (hk.lmode)  
         ) mode_held,  
         DECODE (  
            wk.request,  
            0, 'None',  
            1, 'NULL',  
            2, 'ROW-S (SS)',  
            3, 'ROW-X (SX)',  
            4, 'SHARE',  
            5, 'S/ROW-X (SSX)',  
            6, 'EXCLUSIVE',  
            TO_CHAR (wk.request)  
         ) mode_requested,  
       object_name ,  
         TO_CHAR (hk.id1) lock_id1,  
         TO_CHAR (hk.id2) lock_id2  
FROM     v$lock hk, v$session bs, v$lock wk, v$session ws ,  V$LOCKED_OBJECT a ,  
dba_objects b  
WHERE    hk.BLOCK = 1  
AND      hk.lmode != 0  
AND      hk.lmode != 1  
AND      wk.request != 0  
AND      wk.TYPE(+) = hk.TYPE  
AND      wk.id1(+) = hk.id1  
AND      wk.id2(+) = hk.id2  
AND      hk.sid = bs.sid(+)  
AND      wk.sid = ws.sid(+)  
AND      a.object_id=b.object_id  
AND      hk.sid=a.session_id  
ORDER BY 1;
Plus simplement, comment détecter si une table est verrouillée :

Code sql : Sélectionner tout 
1
2
3
4
SELECT session_id , oracle_username , os_user_name 
  FROM V$LOCKED_OBJECT VLO INNER JOIN dba_objects DO ON VLO.object_id = DO.object_id 
 WHERE object_name = 'object_name' 
   AND owner = 'owner';

Mis à jour le 18 septembre 2006 Jaouad
HautPage
Comment positionner la valeur d'une colonne à sa valeur par défaut ?

À partir d'Oracle 9i, on peut utiliser le mot-clé default pour spécifier la valeur par défaut d'une colonne :

Code sql : Sélectionner tout 
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
SQL> create table TEST ( 
  2     x number default 5, 
  3     y number default -3 
  4  ); 
  
Table created. 
  
SQL> insert into TEST 
  2  values (default, default); 
  
1 row created. 
  
SQL> select * from TEST; 
  
         X          Y 
---------- ---------- 
         5         -3 
  
SQL> update TEST 
  2  set    x = -5, 
  3         y = 0; 
  
1 row updated. 
  
SQL> select * from TEST; 
  
         X          Y 
---------- ---------- 
        -5          0 
  
SQL> update TEST 
  2  set    x = default, 
  3         y = default; 
  
1 row updated. 
  
SQL> select * from TEST; 
  
         X          Y 
---------- ---------- 
         5         -3 
  
SQL> alter table TEST modify ( 
  2     x default 0, 
  3     y default null 
  4  ); 
  
Table altered. 
  
SQL> update TEST 
  2  set    x = default, 
  3         y = default; 
  
1 row updated. 
  
SQL> select * from TEST; 
  
         X          Y 
---------- ---------- 
         0

Mis à jour le 30 novembre 2004 lalystar
HautPage
Comment différencier NULL et chaîne vide ?

Comme le montre l'exemple suivant, lors de vos INSERT/UPDATE, Oracle retransforme les chaînes vide en valeur nulles, ce qui peut être gênant fonctionnellement, en particulier pour des applications multi-SGBD.

Code sql : Sélectionner tout 
1
2
3
4
5
CREATE TABLE dvp ( a VARCHAR2( 10 ) ) ; 
INSERT INTO dvp VALUES (NULL) ;  
INSERT INTO dvp VALUES ('DVP') ;  
INSERT INTO dvp VALUES ('') ;  
COMMIT;
Code sql : Sélectionner tout 
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
SQL> SELECT '-' ||a FROM dvp ;  
  
'-'||A 
----------- 
- 
-DVP 
- 
  
SQL> SELECT COUNT(*) FROM dvp ;  
  
  COUNT(*) 
---------- 
         3 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a IS NULL;  
  
  COUNT(*) 
---------- 
         2 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a = '' ;  
  
  COUNT(*) 
---------- 
         0 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a IS NOT NULL ;  
  
  COUNT(*) 
---------- 
         1 
  
SQL> SELECT COUNT(*) FROM dvp WHERE a != '' ;  
  
  COUNT(*) 
---------- 
         0
Une solution consiste à utiliser un caractère ou une chaîne de susbsitution, afin que votre applicatif puisse gérer ces informations de manière distincte. Le plus simple, si c'est possible, est d'utiliser le caractère espace, ce qui donne, si l'on continue sur l'exemple précédent :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
SQL> insert into dvp values (' ') ; 
  
1 ligne créée. 
  
SQL> commit; 
  
Validation effectuée. 
  
SQL> select count (*) from dvp ; 
  
COUNT(*) 
---------- 
4 
  
SQL> select count(*) from dvp where a is null ;
Attention tout de même :

Oracle® Database SQL Reference 10g Release 2 (10.2) :
Note: Oracle Database currently treats a character value with a length of zero as null. However, this may not continue to be true in future releases, and Oracle recommends that you do not treat empty strings the same as nulls.

Mis à jour le 18 septembre 2006 Jaouad laurentschneider
HautPage
Comment insérer ou mettre à jour des données sans savoir si les enregistrements correspondants existent déjà ?

Grâce à l'instruction MERGE, à partir de la 9i. Voici sa syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
MERGE INTO Table1 T1 
  USING (SELECT Id, Meschamps FROM Table2) T2 
    ON ( T1.Id = T2.Id ) -- Condition de correspondance 
WHEN MATCHED THEN -- Si Vraie 
  UPDATE SET T1.Meschamps = T2.Meschamps 
WHEN NOT MATCHED THEN -- Si faux 
  INSERT (T1.ID, T1.MesChamps) VALUES ( T2.ID, T2.MesChamps);
Prenons l'exemple d'une table "Article" tout ce qu'il y a de plus classique :

  • Une clé (PK)
  • une référence (UQ)
  • un libellé
  • un prix

La clé primaire est renseignée à l'aide d'une séquence.

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
13
CREATE Table Article ( 
Id Number (10), 
Refe VARCHAR2 (16), 
Libe VARCHAR2 (64), 
Prix NUMBER (12,2)); 
  
CREATE SEQUENCE Seq_Id_Article START WITH 1 INCREMENT BY 1; 
  
INSERT INTO Article VALUES (Seq_Id_Article.NextVal, '001', 'Marteau',   7.46); 
INSERT INTO Article VALUES (Seq_Id_Article.NextVal, '002', 'Tournevis', 3.83); 
INSERT INTO Article VALUES (Seq_Id_Article.NextVal, '004', 'Lime',      5.09); 
  
COMMIT;
Sa sœur Temp_Article reçoit un fichier de MAJ à jour des prix, sans clé et pouvant contenir de nouveaux articles :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
CREATE Table Temp_Article ( 
Refe VARCHAR2 (16), 
Libe VARCHAR2 (64), 
Prix NUMBER (12,2)); 
  
INSERT INTO Temp_Article VALUES ('001', 'Marteau',   7.27); 
INSERT INTO Temp_Article VALUES ('002', 'Tournevis', 3.81); 
INSERT INTO Temp_Article VALUES ('003', 'Pince',     2.67); 
INSERT INTO Temp_Article VALUES ('004', 'Lime',      5.35); 
INSERT INTO Temp_Article VALUES ('005', 'Clé',       4.91); 
COMMIT;
La commande MERGE va nous servir à faire la MAJ des prix et l'intégration des nouveaux articles en une seule commande :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> MERGE INTO Article A 
  2    USING (SELECT Refe, Libe, prix FROM Temp_Article) T 
  3       ON (A.Refe = T.Refe) 
  4  WHEN MATCHED THEN  
  5    UPDATE SET A.Prix = T.Prix, A.Libe = T.Libe 
  6  WHEN NOT MATCHED THEN  
  7    INSERT (A.Id, A.Refe, A.Libe, A.Prix) VALUES (Seq_Id_Article.NextVal, T.Refe, T.Libe, T.Prix); 
  
5 lignes fusionnées. 
  
SQL> SELECT * FROM Article; 
  
        ID REFE             LIBE             PRIX 
---------- ---------------- ---------------- ------- 
         1 001              Marteau          7,27 
         2 002              Tournevis        3,81 
         3 004              Lime             5,35 
         7 003              Pince            2,67 
         8 005              Clé              4,91
Voici la page Oracle sur laquelle vous trouverez toutes les informations utiles à propos de cette commande :
http://download-west.oracle.com/docs...htm#SQLRF01606

Use the MERGE statement to select rows from one or more sources for update or insertion into one or more tables. You can specify conditions to determine whether to update or insert into the target tables.

This statement is a convenient way to combine multiple operations. It lets you avoid multiple INSERT, UPDATE, and DELETE DML statements.

MERGE is a deterministic statement. That is, you cannot update the same row of the target table multiple times in the same MERGE statement.

Mis à jour le 18 septembre 2006 Xo
http://download-west.oracle.com/docs...htm#SQLRF01606
HautPage
Comment empêcher l'exécution d'une requête non ré écrite ?

Grâce au hint apparue avec la version 10g :

Code sql : Sélectionner tout 
/*+ REWRITE_OR_ERROR */

Mis à jour le 18 septembre 2006 Jaouad
HautPage
Comment construire une requête récursive ?

Oracle permet de gérer le concept de requêtes récursives, à l'aide des clauses suivantes :

  • START WITH Champ = {Valeur} : Cette clause permet de préciser le(s) nœud(s) de l'arborescence à partir duquel ou desquels on souhaite lister les éléments,
  • CONNECT BY PRIOR Id = Id_Parent : Cette clause indique sur quels champs se baser pour parcourir l'arborescence,

Depuis la version 9i, vous pouvez utiliser un pseudo champ LEVEL afin de connaître le niveau de chaque enregistrement.

En voici un exemple, grâce à une table Catégorie comprenant les champs suivants :

  • Une Clé (Id)
  • Un Libellé (Libe)
  • La clé de la catégorie parent (Id_Parent)

Code sql : Sélectionner tout 
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
CREATE TABLE Categorie ( 
    Id NUMBER (4), 
    Libe VARCHAR2 (16), 
    Id_Parent NUMBER (4) 
); 
  
-- Les clés sont attribuées de manière hiérarchique pour plus de clarté, ce qui est bien entendu rarement le cas dans la réalité ... 
INSERT INTO Categorie VALUES (   1, 'Librairie'     , NULL); 
INSERT INTO Categorie VALUES (  11, 'Revue'         , 1); 
INSERT INTO Categorie VALUES ( 111, 'Quotidien'     , 11); 
INSERT INTO Categorie VALUES ( 112, 'Hebdomadaire'  , 11); 
INSERT INTO Categorie VALUES ( 111, 'Mensuel'       , 11); 
INSERT INTO Categorie VALUES (  12, 'Roman'         , 1); 
INSERT INTO Categorie VALUES (  13, 'Bande Dessinée', 1); 
  
INSERT INTO Categorie VALUES (   2, 'Alimentaire'   , NULL); 
INSERT INTO Categorie VALUES (  21, 'Boisson'       , 2); 
INSERT INTO Categorie VALUES ( 211, 'Alcool'        , 21); 
INSERT INTO Categorie VALUES ( 212, 'Jus de fruits' , 21); 
INSERT INTO Categorie VALUES ( 213, 'Alcool'        , 21); 
INSERT INTO Categorie VALUES (2131, 'Vins'          , 213); 
INSERT INTO Categorie VALUES (2132, 'Bière'         , 213); 
INSERT INTO Categorie VALUES (2133, 'Apéritif'      , 213); 
INSERT INTO Categorie VALUES (  22, 'Conserve'      , 2); 
INSERT INTO Categorie VALUES (  23, 'Frais'         , 2); 
  
COMMIT;
Voici la liste des catégories dépendant de la catégorie 1 : Librairie :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SELECT Id, Libe, LEVEL, Id_Parent 
  2    FROM Categorie 
  3   START WITH Id = 1 
  4  CONNECT BY PRIOR Id = Id_Parent; 
  
        ID LIBE                  LEVEL  ID_PARENT 
---------- ---------------- ---------- ---------- 
         1 Librairie                 1 
        11 Revue                     2          1 
       111 Quotidien                 3         11 
       112 Hebdomadaire              3         11 
       111 Mensuel                   3         11 
        12 Roman                     2          1 
        13 Bande Dessinée            2          1 
  
7 ligne(s) sélectionnée(s).
À partir de la 9i, vous pouvez également utilisez la fonction SYS_CONNECT_BY_PATH (Champ, séparateur), qui permet de concaténer les différents valeurs du champ spécifié pour connaître le chemin entre l'élément concerné et le nœud racine :

Code sql : Sélectionner tout 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
SQL> SELECT SYS_CONNECT_BY_PATH(Libe, '-') 
  2    FROM Categorie 
  3   START WITH Id = 2 
  4  CONNECT BY PRIOR Id = Id_Parent; 
  
SYS_CONNECT_BY_PATH(LIBE,'-') 
------------------------------------------ 
-Alimentaire 
-Alimentaire-Boisson 
-Alimentaire-Boisson-Alcool 
-Alimentaire-Boisson-Jus de fruits 
-Alimentaire-Boisson-Alcool 
-Alimentaire-Boisson-Alcool-Vins 
-Alimentaire-Boisson-Alcool-Bière 
-Alimentaire-Boisson-Alcool-Apéritif 
-Alimentaire-Conserve 
-Alimentaire-Frais 
  
10 ligne(s) sélectionnée(s).

Mis à jour le 18 septembre 2006 Xo
HautPage
Quels sont les raccourcis sous SQL*Plus

@ désigne la variable ORACLE_SID courante. Est valable dans les scripts SQL, dans divers paramètres de l'INIT.ORA, dans des scripts RMAN notamment.

? désigne la variable ORACLE_HOME courante. Est valable dans les scripts SQL, dans divers paramètres de l'INIT.ORA, dans des scripts RMAN notamment.

! équivalent de la commande HOST de SQL*Plus, permet d'appeler le shell, sous Unix uniquement

$ équivalent de la commande HOST de SQL*Plus, permet d'appeler le shell, sous Windows uniquement

& permet de désigner une variable SQL*Plus

start et @ permet de lancer un script

define permet de définir une variable sous Sql*Plus , sans argument elle donne toutes les variables déja défini

& permet de définir une variable, && permet de la définir une fois pour toute et pour toute la session.

ed permet de lancer le buffer

l permet de voir sans l'executer le dernier ordre SQL

r permet de voir et executer le dernier ordre SQL

c:mot1:mot2 permet de changer toutes les occurences du mot1 par mot2 dans le dernier ordre SQL

Mis à jour le 15 octobre 2006 Jaouad orafrance Pomalaix
HautPage
Quelles sont les principales variables d'environnement de SQL Plus ?

Voici les variables d'environnement les plus communes :

  • define : caractère utilisé pour la substitution des variables (par défaut "&")

syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
5
6
-- gère les caractères de substitution 
SET define ON 
-- les caractères de substitutions ne sont pas gérés 
SET define off 
-- le caractère de substition est @ 
SET define @

- linesize : nombre de caractères par lignes
NB : la ligne est complétée par des espaces pour atteindre ce nombre de caractères.
syntaxe :

Code sql : Sélectionner tout 
1
2
-- mets le nom de caractère par ligne à 130  
SET linesize 130

- numformat : format par défaut des numériques à l'affichage.
syntaxe :

Code sql : Sélectionner tout 
SET numformat 999G999D99

- numwidth : largeur par défaut des numériques
syntaxe :

Code sql : Sélectionner tout 
SET numwidth 8

- pagesize : nombre de lignes de détail (entête nom compris)
syntaxe :

Code sql : Sélectionner tout 
SET pagesize 60

- scan : contrôle la présence de variables de substitution
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
-- contrôle la présence de variables de substitions 
SET scan ON 
-- ne contrôle pas la présence de variables de substitions 
SET scan OFF

- tab : utilisation des tabulations
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
-- utilisation des tabulations 
SET tab ON 
-- non utilisation des tabulations 
SET tab OFF

- trimspool : suppression des espaces en fin de lignes
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
-- supprime les espaces en fin de lignes 
SET trimspool ON 
-- laisse les espaces en fin de lignes 
SET trimspool OFF

- verify : liste la commande avant son exécution
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
-- liste la commande avant son exécution 
SET verify ON 
-- la commande n'est pas affichée avant son exécution 
SET verify OFF

- feed : affiche le nombre de lignes affectées par l'ordre SQL
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
-- affichage du nombre de lignes affectées 
SET feed ON 
-- le nombre de lignes affectées n'est pas affiché 
SET feed OFF

- term : affiche le résultat des commandes d'un script
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
--  affichage du résultat 
SET term ON  
--  non affichage du résultat 
SET term OFF

- heading : affichage de l'entête du résultat
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
--  affichage de l’entête 
SET heading ON  
--  non affichage de l’entête 
SET head OFF

- space : nombre d'espaces entre deux colonnes
syntaxe :

Code sql : Sélectionner tout 
1
2
-- 2 espaces entre 2 colonnes  
SET space 2

- echo : affichage des commandes d'un script à l'exécution
syntaxe :

Code sql : Sélectionner tout 
1
2
3
4
--  affichage de la commande 
SET echo ON  
--  non affichage de la commande 
SET echo OFF

NB : Ce sont les principales, il en existe d'autres (SQL*Plus : Menu Option-> Environment) dont vous trouverez l'explication dans la documentation Oracle.

Mis à jour le 15 octobre 2006 plaineR
HautPage
Comment indexer un prédicat 'IS NOT NULL'?

Avec Oracle, un index normal (c'est à dire non bitmap) n'indexe pas les entrées nulles.
Donc si j'ai un index sur la colonne NUM d'une table DEMO, alors l'index ne peut pas être utilisé pour la requête suivante:

Code SQL : Sélectionner tout 
select * from DEMO where NUM =10 or NUM is null;

même si il n'y a que très peu de lignes qui on NUM =10 et très peu qui ont NUM à null.

L'astuce revient à rajouter dans l'index une colonne qui déclarée NOT NULL. Alors l'optimiseur saura qu'il n'y a pas de lignes avec des entrées d'index complètement nulles et pourra utiliser l'index pour cet accès. Et si on a pas besoin d'une colonne de plus, alors on peut rajouter une constante. La plus petite, le nombre zéro qui ne fait qu'un octet, fait l'affaire:
Code SQL : Sélectionner tout 
1
2
3
4
SQL> create table DEMO as select rownum id,case when rownum>2 then rownum end num from xmltable('1 to 1000000'); 
Table created. 
SQL> create index DEMO_NUM_OR_NULL on DEMO(NUM,0); 
Index created.

et voici le plan d'exécution de la requête en question:
Code SQL : Sélectionner tout 
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
SQL> select * from DEMO where num=10 or num is null; 
  
        ID        NUM 
---------- ---------- 
        10         10 
         1 
         2 
  
Execution Plan 
---------------------------------------------------------- 
Plan hash value: 2956564463 
  
-------------------------------------------------------------------------------------- 
| Id  | Operation                            | Name             | Rows  | Cost (%CPU)| 
-------------------------------------------------------------------------------------- 
|   0 | SELECT STATEMENT                     |                  |     3 |     8   (0)| 
|   1 |  CONCATENATION                       |                  |       |            | 
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEMO             |     1 |     4   (0)| 
|*  3 |    INDEX RANGE SCAN                  | DEMO_NUM_OR_NULL |     1 |     3   (0)| 
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| DEMO             |     2 |     4   (0)| 
|*  5 |    INDEX RANGE SCAN                  | DEMO_NUM_OR_NULL |     2 |     3   (0)| 
-------------------------------------------------------------------------------------- 
  
Predicate Information (identified by operation id): 
--------------------------------------------------- 
   3 - access("NUM"=10) 
   5 - access("NUM" IS NULL) 
       filter(LNNVL("NUM"=10))

L'optimiseur a transformé le 'OR' en 'UNION' et a un accès par index pour les deux cas.

Mis à jour le 20 juillet 2015 pachot

Proposer une nouvelle réponse sur la FAQ

Ce n'est pas l'endroit pour poser des questions, allez plutôt sur le forum de la rubrique pour ça


Réponse à la question

Liens sous la question
précédent sommaire suivant
 

Les sources présentées sur cette page sont libres de droits et vous pouvez les utiliser à votre convenance. Par contre, la page de présentation constitue une œuvre intellectuelle protégée par les droits d'auteur. Copyright © 2024 Developpez Developpez LLC. Tous droits réservés Developpez LLC. Aucune reproduction, même partielle, ne peut être faite de ce site et de l'ensemble de son contenu : textes, documents et images sans l'autorisation expresse de Developpez LLC. Sinon vous encourez selon la loi jusqu'à trois ans de prison et jusqu'à 300 000 € de dommages et intérêts.