Index créé par une contrainte d'intégrité ou non : comment les identifier? - Index created by an integrity constraint or not
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
/https%3A%2F%2Fassets.over-blog.com%2Ft%2Fcedistic%2Fcamera.png)
/https%3A%2F%2Fprofilepics.canalblog.com%2Fprofilepics%2F1%2F5%2F1555182.jpg)