Introduction
Est-ce que vous savez comment distinguer les index créés via les contraintes d'intégrité PRIMARY KEY et UNIQUE des index créés à la demande? Non? OK, on va voir ça, en route vers le coeur d'Oracle :-)


 
Points d'attention
Aucun.



Base de tests
N'importe quelle base Oracle.



Exemples
============================================================================================
La table du dictionnaire de données IND$
============================================================================================

Premier point, il n'existe aucun attribut dans DBA_INDEXES disant si un index a été créé par une contrainte d'intégrité ou non. Une fois de plus, il va falloir se tourner vers le vrai dictionnaire de données et pas vers ses vues puisque celles-ci ne récupèrent pas tous les attributs des tables sous-jacentes. Pour notre besoin, cette table s'appelle IND$.

Dans $ORACLE_HOME/rdbms/admin/dcore.bsq, il y a le script de création de cette table SYS.IND$. La colonne PROPERTY est celle qui va nous donner l'info : si la valeur en hexadecimal vaut 0x1000, alors "The index was created by a constraint".

Si bitand(PROPERTY, 4096)=4096 (correspondant au 1000 hexa), ça signifie que l'index a été créé automatiquement donc lié à une contrainte d'intégrité. De plus, si bitand(PROPERTY, 1)=1, il s'agit d'un index unique. Donc si l'index est créé automatiquement et unique (c'est à dire bitand(PROPERTY, 4097)=4097), il sera supprimé par le DROP CONSTRAINT d'une contrainte PRIMARY KEY ou UNIQUE.


C'est confus? Allez, on teste! Extrait du fichier dcore.bsq et composition de notre base.
     create table ind$   /* index table */
     ...
     property      number not null,    /* immutable flags for life of the index */
             /* unique : 0x01 */
             /* partitioned : 0x02 */
             /* reverse : 0x04 */
             /* compressed : 0x08 */
             /* functional : 0x10 */
             /* temporary table index: 0x20 */
             /* session-specific temporary table index: 0x40 */
             /* index on embedded adt: 0x80 */
             /* user said to check max length at runtime: 0x0100 */
             /* domain index on IOT: 0x0200 */
             /* join index : 0x0400 */
             /* system managed domain index : 0x0800 */
             /* The index was created by a constraint : 0x1000 */
             /* The index was created by create MV : 0x2000 */
             /* composite domain index : 0x8000 */


     SQL> select property, count(*) from sys.ind$ group by property order by 1;
     PROPERTY   COUNT(*)
     ---------- ----------
         0        521
         1       1025
         2         65
         3          6
        16         21
        17         11
        32          1
        96          3
        97          3
      4096          1
      4097        667
      4099         44
      4113          2
      4129          4
     16384          1
     16385          1
      16 lignes selectionnees.

============================================================================================
Création des différents types d'index
============================================================================================
 
On créé une table avec une PK et une contrainte UNIQUE nommées; cela va créer deux index qui seront utilisés par Oracle pour valider l'unicité des valeurs de ces colonnes.
     CREATE TABLE zztab
     (
       id number(10) CONSTRAINT zztab_pk_id PRIMARY KEY,
       name varchar2(50 char) CONSTRAINT zztab_uniq_name UNIQUE,
       mail varchar2(100 char)
     );
 
Insertion de données et commit.
     SQL> insert into zztab values(1,'test1', 'mail1');
     SQL> insert into zztab values(2, 'test2', 'mail2');
     SQL> commit;
 
On crée maintenant un index UNIQUE mais non lié à une contrainte : de la sorte nous avons trois index, des trois types qui nous intéressent.
     SQL> CREATE INDEX zztab_uniq_mail ON zztab(mail);
 
Les  index ont été créés.
     SQL> select table_name, INDEX_NAME, INDEX_TYPE, UNIQUENESS from user_indexes order by 1, 2;
     TABLE_NAME       INDEX_NAME           INDEX_TYPE        UNIQUENES
     ------------------------------------------------------------------
     ZZTAB            ZZTAB_PK_ID          NORMAL            UNIQUE
     ZZTAB            ZZTAB_UNIQ_MAIL      NORMAL            NONUNIQUE
     ZZTAB            ZZTAB_UNIQ_NAME      NORMAL            UNIQUE
 
ID de la table et des index.
     SQL> select object_id from dba_objects where owner = 'ZZTEST' and OBJECT_NAME = 'ZZTAB';
     OBJECT_ID
     ----------
     151254
 
     SQL> select object_id, object_name from dba_objects where owner = 'ZZTEST' and OBJECT_NAME in ('ZZTAB_PK_ID', 'ZZTAB_UNIQ_NAME', 'ZZTAB_UNIQ_MAIL') order by 2;               
     OBJECT_ID     OBJECT_NAME
     ---------- ---------------
     151255         ZZTAB_PK_ID
     151257         ZZTAB_UNIQ_MAIL
     151256         ZZTAB_UNIQ_NAME
 
On vérifie maintenant dans IND$ la valeur du champ PROPERTY.
Bingo, il y a bien une  différence entre un index lié à une contrainte (valeur 4096 ou 4097 si index UNIQUE) et un index non lié à une contrainte (valeur <> de 4096 et de 4097).
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151255, 151257, 151256) order by O.object_name;
     OBJ#     OBJECT_NAME            PROPERTY
     ---------- -------------------- ----------
     151255     ZZTAB_PK_ID                4097
     151257     ZZTAB_UNIQ_MAIL               0
     151256     ZZTAB_UNIQ_NAME            4097


============================================================================================
Liens entre contraintes et index
============================================================================================
Si on droppe les contraintes PK et UNIQUE : est-ce que les index liés sont supprimés en même temps?
     SQL> alter table zztab drop constraint ZZTAB_PK_ID;
     SQL> alter table zztab drop constraint ZZTAB_UNIQ_NAME;
 
Ces index ont bien été supprimés avec leur contrainte : normal puisque leur existence était liée justement à cette contrainte.
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME    INDEX_NAME
     ---------------------------
     ZZTAB       ZZTAB_UNIQ_MAIL
 
Maintenant, créons un index unique sur une colonne sans passer par une contrainte.
     SQL> CREATE UNIQUE INDEX ZZTAB_PK_ID_NO_CONS ON zztab(id);
 
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME   INDEX_NAME
     -------------------------------
     ZZTAB        ZZTAB_PK_ID_NO_CONS
     ZZTAB        ZZTAB_UNIQ_MAIL
 
     SQL> select object_id from dba_objects where owner = 'ZZTEST' and OBJECT_NAME = 'ZZTAB_PK_ID_NO_CONS';          
     OBJECT_ID
     ----------
     151258
 
Vérification dans IND$ la valeur du champ PROPERTY. Tiens, quand je crée un index non UNIQUE, la colonne PROPERTY vaut 0 et quand l'index est UNIQUE, cette colonne vaut 1.
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151258);
     OBJ#       OBJECT_NAME            PROPERTY
     ---------- -------------------- ----------
     151258     ZZTAB_PK_ID_NO_CONS           1
 
Maintenant on crée une contrainte PK sur la colonne indexée et on vérifie si c'est cet index existant qui est utilisé et non pas un nouveau créé spécialement par Oracle.
Effectivement, aucun nouvel index n'a été créé par Oracle.
     SQL> ALTER TABLE zztab ADD CONSTRAINT ZZTAB_PK_ID PRIMARY KEY (id);
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME   INDEX_NAME
     -------------------------------
     ZZTAB        ZZTAB_PK_ID_NO_CONS
     ZZTAB        ZZTAB_UNIQ_MAIL
 
Il n'y a qu'une contrainte dans la table.
     SQL> select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints order by 1;
     TABLE_NAME    CONSTRAINT_NAME      C
     -------------------- --------------------
     ZZTAB         ZZTAB_PK_ID          P

     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151258, 151257);
     OBJ#       OBJECT_NAME            PROPERTY
     ------- -------------------- ----------
     151257     ZZTAB_UNIQ_MAIL               0
     151258     ZZTAB_PK_ID_NO_CONS           1
 
Suppression de la contrainte d'intégrité PK : l'index associé n'est pas supprimé, à la différence du premier test.
     SQL> ALTER TABLE zztab DROP CONSTRAINT ZZTAB_PK_ID ;
     SQL> select TABLE_NAME, CONSTRAINT_NAME, CONSTRAINT_TYPE from user_constraints order by 1;
     aucune ligne selectionnee
 
C'est OK : la suppression de la contrainte PRIMARY KEY n'a pas supprimé l'index dans ce test-ci.
     SQL> select table_name, INDEX_NAME from user_indexes order by 1, 2;
     TABLE_NAME   INDEX_NAME
     -------------------------------
     ZZTAB        ZZTAB_PK_ID_NO_CONS
     ZZTAB        ZZTAB_UNIQ_MAIL
 
Et ses caractéristiques n'ont pas changé.
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (151258, 151257);
     OBJ#     OBJECT_NAME            PROPERTY
     ---------- -------------------- ----------
     151257   ZZTAB_UNIQ_MAIL               0
     151258   ZZTAB_PK_ID_NO_CONS           1
 
Allez, on refait un test : ajout d'une colonne avec une contrainte unique.
     SQL> SELECT I.obj#, O.object_name, I.property FROM sys.ind$ I, dba_objects O WHERE I.obj#=O.object_id and I.obj# in (select object_id from user_objects where OBJECT_NAME in (select index_name from user_indexes where table_name = 'ZZTAB')) order by 2;
     OBJ#       OBJECT_NAME                PROPERTY
     ---------- ----------------------------------------
     151258     ZZTAB_PK_ID_NO_CONS        1
     151257     ZZTAB_UNIQ_MAIL            0
 
     SQL> ALTER TABLE zztab ADD id02 NUMBER(10);
     SQL> ALTER TABLE zztab MODIFY id02 CONSTRAINT uniq_id02 UNIQUE;
 
Voilà, on a tous les types d'index : PRIMARY KEY, UNIQUE, dépendant ou non d'une contrainte.
     SQL> SELECT I.obj#, O.object_name, I.property
     FROM sys.ind$ I, dba_objects O
     WHERE I.obj#=O.object_id and I.obj# in
         (select object_id
          from user_objects
          where OBJECT_NAME in
            (    select index_name
                from user_indexes
                where table_name = 'ZZTAB'))
     order by 2;
     OBJ#       OBJECT_NAME                   PROPERTY
     ---------- --------------------------------------
     151268     UNIQ_ID02                     4097
     151258     ZZTAB_PK_ID_NO_CONS              1
     151257     ZZTAB_UNIQ_MAIL                  0


============================================================================================
Les ordres SQL pour identifier les index créés ou non par des contraintes
============================================================================================
Voici l'ordre pour avoir les index de ma table dépendant d'une contrainte d'intégrité PRIMARY KEY ou UNIQUE.
     SQL> SELECT I.obj#, O.object_name, I.property
     FROM sys.ind$ I, dba_objects O
     WHERE I.obj#=O.object_id and I.obj# in
    (select object_id
     from user_objects
     where OBJECT_NAME in
            (select index_name
             from user_indexes
             where table_name = 'ZZTAB'))
     and property = 4097
     order by 2;
     OBJ#       OBJECT_NAME        PROPERTY
     ---------- ----------------------------
     151268     UNIQ_ID02          4097
 
Voici l'ordre pour avoir les index de ma table ne dépendant pas d'une contrainte d'intégrité PRIMARY KEY ou UNIQUE.
     SQL> SELECT I.obj#, O.object_name, I.property
     FROM sys.ind$ I, dba_objects O
     WHERE I.obj#=O.object_id and I.obj# in
         (select object_id
          from user_objects
          where OBJECT_NAME in
            (select index_name
             from user_indexes
             where table_name = 'ZZTAB'))
     and property <> 4097
     order by 2;

     OBJ#       OBJECT_NAME                 PROPERTY
     ---------- -----------------------------------------
     151258     ZZTAB_PK_ID_NO_CONS         1
     151257     ZZTAB_UNIQ_MAIL             0