Discussion :

[new_wave]

Bonjour,
J'ai crée la table TITLE_COPY avec la syntaxe suivante

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE TABLE title_copy (copy_id NUMBER(10),
			title_id NUMBER(10)
			CONSTRAINT title_copy_title_id_fk REFERENCES title(title_id),
			STATUS VARCHAR2(15)
			CONSTRAINT title_copy_status_nn NOT NULL
			CONSTRAINT title_copy_status_ck CHECK(status IN('AVAILABLE','DESTROYED','RENTED','RESERVED')),
			CONSTRAINT title_copy_copy_title_id_pk PRIMARY KEY(copy_id));

On voit que la contrainte de clé étrangère est spécifiée simplement en indiquant CONSTRAINT title_copy_title_id_fk REFERENCES title(title_id),
et pas CONSTRAINT title_copy_title_id_fk FOREIGN KEY( title_id)REFERENCES title(title_id).
En effet, en utilisant la deuxième façon ( avec FOREIGN KEY) j'obtiens une erreur.

Je remarque que lorsque je crée les contraintes tout à la fin comme dans cet exemple, je n'ai pas de problème( message d'erreur).
Pouvez vous m'expliquer je reçois une erreur lorsque j'utilise la deuxième syntaxe( avec foreign key)sans être à la fin de la création de la table alors que je n'en reçois pas lorsque je crée la contrainte à la fin .

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
 
CREATE TABLE RESERVATION (reservation_id number(10) NOT NULL ,
			member_id NUMBER(10),
			title_id NUMBER(10),
			res_date DATE NOT NULL,
			CONSTRAINT reservation_title_id_fk FOREIGN KEY(title_id) REFERENCES title(title_id),
			CONSTRAINT reservation_member_id_fk FOREIGN KEY(member_id) REFERENCES member(member_id),
			CONSTRAINT res_tit_rdate_mem_id_pk PRIMARY KEY(res_date,member_id,title_id,reservation_id ));

Merci beaucoup à vous.
Par ailleurs, si en créant les contraintes de primary key et foreign key je ne nomme pas expressement les contraintes, comment puis je les retrouver pour les supprimer.
J'ai essayé de recupérer leur nom en allant par la commande suivante mais sans succès

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
 
 
select constraint_name, table_name, constraint_type from user_constraints;

Pour finir, lorsqu'une contrainte est activée , la table correspondante est vérouillée jusqu'à ce que la vérifiçcation soit terminée.
Cela signifie -t-il qu'aucun utilisateur ne peut mettre à jour des données dans la table avant que la verification par la contrainte ne soit effectuée.
Si oui, est ce aussi valable pour la sélection, l'insertion (le verrouillage s'effectue t-il également pendant ces opérations).

Merci beaucoup encore de votre aide.

Cordialement.

Nathalie

[pifor]

Quelle est la version exacte d'Oracle qui est utilisée (4 chiffres SVP) ?

[new_wave]

Bonsoir ,

La version d'Oracle est 8i 8.1.7.0.0 ; j'ai fait le test avec la version 11g et le résultat est le même.

Cordialement.
Nathalie

[pifor]

Si la contrainte de clé étrangère est définie au niveau de la colonne, il n'est pas nécessaire de définir quelle est la colonne concernée avec FOREIGN KEY. Par contre, si la colonne est définie au niveau de la table, il faut bien dire définir quelle est la colonne concernée (ou quelles sont les colonnes concernées) avec FOREIGN KEY.

Si les contraintes ne sont pas nommées, il doit être possible de retrouver les colonnes concernées avec une jointure entre USER_CONSTRAINTS et USER_CONS_COLUMNS (testé avec Oracle 10.2.0.1/XE):

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
 
  1  select cc.constraint_name, cc.column_name
  2  from user_cons_columns cc, user_constraints c
  3  where
  4* c.table_name = 'RESERVATION' and c.constraint_type='R' and cc.constraint_name = c.constraint_name
SQL> /
 
CONSTRAINT_NAME
------------------------------
COLUMN_NAME
--------------------------------------------------------------------------------
SYS_C005414
MEMBER_ID
 
SYS_C005415
TITLE_ID

Du point de vue du verrouillage, une instruction SELECT (sauf avec for UPDATE) ne verrouille pas de ligne dans les tables de la clause FROM. INSERT, UDPATE, DELETE sur une table enfant n'entraînent pas de verrouillage exclusif au niveau de la table enfant (qu'il y ait une clé étrangère ou non) mais un verrouillage exclusif des lignes concernées dans la table enfant. Par contre, si la clé étrangère n'est pas indexée, il peut y avoir des verrous sur la table enfant lorsque la table parent est modifiée: exemple en 10.2: http://download.oracle.com/docs/cd/B...htm#sthref3103

[mnitu]

... Par contre, si la clé étrangère n'est pas indexée, il peut y avoir des verrous sur la table parent.

Salut pifor,

Les modifications sur la table parent peuvent impliquer des verrous sur la table fille mais pas le contraire, donc peux-tu donner un exemple ?

[pifor]

Salut mnitu,

En effet je me suis trompé. Merci pour la correction.

[new_wave]

Bonjour,


Lors de mon dernier message, je vous informais que j'ai appris que lorsqu'une contrainte est activée sur une table, la table est verrouillée jusqu'à ce que la vérification soit terminée.

Si une table est une table enfant, qui contient une clé étrangère, donc, elle est verrouillée jusqu'à ce que la vérification des valeurs insérées ou mises à jour soit faite( les valeurs insérées doivent correspondre à des valeurs existantes dans la table parente, au niveau de la primary key)
Je posais la question de savoir en quoi consiste exactement le verrouillage.

La réponse reçue est , si j'ai bien compris, que lors des requêtes de sélection sur la table enfant, il n'y a pas de verrouillage( est-ce bien exact?).
Par contre pour les requêtes de type INSERT, UPDATE ou DELETE, il y a un verrouillage uniquement des lignes concernées par la requête dans la table enfant, comme il est dit ci-dessous

INSERT, UDPATE, DELETE sur une table enfant n'entraînent pas de verrouillage exclusif au niveau de la table enfant (qu'il y ait une clé étrangère ou non) mais un verrouillage exclusif des lignes concernées dans la table enfant

Cela veut-il dire que pendant que je fais une requête de type INSERT, UDPATE, DELETE sur une table enfant, je ne peux pas agir sur les lignes concernées par cette requête .
Cela signifie-t-il , par exemple, que si je fais un update sur une ligne de la table enfant, un autre utilisateur ne peut pas la selectionner , ou encore, si je fais un delete sur une ligne, un autre utlisateur ne peut pas faire en même temps de update sur cette ligne?


J'aimerais beaucoup avoir une réponse claire là-dessus.

Merci beaucoup à vous.

Par ailleurs, En quoi une requête de type INSERT nécessite un verrouillage puisqu'elle propose d'insérer une nouvelle ligne.
Elle ne peut donc pas verrouiller une ligne existante.

Ensuite, pifor a indiqué que si la clé étrangère n'est pas indexée( comme c'est le cas pour Oracle si j'ai bien compris) il peut y avoir des verrous sur la table fille mais pas le contraire.

mnitu a repris piforlà dessus.
A t-il voulu dire qu'en fait, des modifications sur la table parent entraînent des verrous sur la table fille mais que la réciproque est aussi vraie.

Merci encore beaucoup à vous de votre aide.

Cordialement.

Nathalie

[pifor]

Bonjour,

Lors de mon dernier message, je vous informais que j'ai appris que lorsqu'une contrainte est activée sur une table, la table est verrouillée jusqu'à ce que la vérification soit terminée.

Si une table est une table enfant, qui contient une clé étrangère, donc, elle est verrouillée jusqu'à ce que la vérification des valeurs insérées ou mises à jour soit faite( les valeurs insérées doivent correspondre à des valeurs existantes dans la table parente, au niveau de la primary key)
Je posais la question de savoir en quoi consiste exactement le verrouillage.-
La réponse reçue est , si j'ai bien compris, que lors des requêtes de sélection sur la table enfant, il n'y a pas de verrouillage( est-ce bien exact?).

Il faut distinguer le verrouillage de niveau ligne et de niveau table. La règle générale dans Oracle est qu'un ordre SELECT (sans la clause FOR UPDATE) ne verrouille pas de ligne dans une table. Mais le simple fait d'utiliser une table dans une requête va poser un verrou d'un certain type sur la table non pas pour bloquer des instructions de type SELECT, INSERT, UPDATE, DELETE mais certaines opérations comme DROP TABLE.

Par contre pour les requêtes de type INSERT, UPDATE ou DELETE, il y a un verrouillage uniquement des lignes concernées par la requête dans la table enfant, comme il est dit ci-dessous

Cela veut-il dire que pendant que je fais une requête de type INSERT, UDPATE, DELETE sur une table enfant, je ne peux pas agir sur les lignes concernées par cette requête .

Cela signifie-t-il , par exemple, que si je fais un update sur une ligne de la table enfant, un autre utilisateur ne peut pas la selectionner , ou encore, si je fais un delete sur une ligne, un autre utlisateur ne peut pas faire en même temps de update sur cette ligne?

Lors de l'exécution de n'importe quelle instruction SQL Oracle prend des verrous de façon automatique ou non en fonction de l'instruction SQL exécutée.

Le mécanisme de consistency read mis en oeuvre dans Oracle garantit qu'une lecture (SELECT sans la clause FOR UPDATE) ne prend aucun verrou de niveau ligne. En revanche une écriture (INSERT, UPDATE, DELETE) déclenche automatiquement une demande de verrou exclusif au niveau ligne. Dans Oracle, les lectures ne bloquent ni les autres lectures, ni les autres écritures. Un écriture bloque une autre écriture uniquement si les lignes concernées sont les mêmes.

Voir ce que dit le Concepts Guide (dont la lecture est vivement recommandée à tout développeur ou DBA) à ce sujet: How Oracle Locks Data. Au sujet du consistency read lire aussi l'article suivant On Transaction Isolation Levels. Enfin pour aller plus loin, il faut lire [ame="http://www.amazon.com/exec/obidos/tg/detail/-/1590595300/qid=1124156571/sr=8-1/ref=pd_bbs_1?v=glance&s=books&n=507846&tag2=asktom03-20"]"Expert Oracle Database Architecture"[/ame] de Tom Kyte qui a été traduit en chinois, en russe et en coréen mais pas en français

[new_wave]

Bonjour,

J'ai repris vos explications .
Voici mes remarques

Vous dites ceci

Par contre pour les requêtes de type INSERT, UPDATE ou DELETE, il y a un verrouillage uniquement des lignes concernées par la requête dans la table enfant.

Vous ajoutez ceci

Mais le simple fait d'utiliser une table dans une requête va poser un verrou d'un certain type sur la table non pas pour bloquer des instructions de type SELECT, INSERT, UPDATE, DELETE mais certaines opérations comme DROP TABLE.

C'est donc qu'une requête insert, update ou delete sur une table enfant provoque également un verrou de niveau table,qui empeche par exemple la table concernée par la requete d'être détruite par un DROP TABLE.
Oui ou Non( j'ai besoin d'une réponse)?

Peut on donc affirmer qu'il y a bien les deux niveaux de verrouillage qui s'appliquent à une table lorsqu'elle est l'objet de requete de type insert, update ou delete.

Par ailleurs, j'ai lu le document en lien ( precedent mail).
D'après ce que j'ai compris, pour les requêtes de type insert, delete et update, le type de verrouilage est row exclusive (rx).
Si j'ai bien compris, ce verrouillage permet d'autres requêtes de type update, insert et delete sur la table concernée mais pas pour les mêmes lignes, elle interdit également un verrouillage qui ne permettrait qu' une lecture seule de la table ou une ecriture seule sur la table.( explications données dans la partie :row exclusive table locks).


Ensuite , dans le paragraphe "default locking for insert, update delete and select for update" il est dit que la transaction qui contient une requête de ce type acquiert le type de verrou row exclusive sur les lignes modifiées.D'autres transactions ne peuvent pas mettre à jour ou supprimer les lignes verrouillées jusqu'à la fin de la requête, ce qui rejoint ce qui a ete dit dans la partie explicatiove de rox exclusive table locks.

Donc est ce là ce que je dois retenir de manière générale concernant le verrouillage d'une table pendant une requete de type insert, delete ou update( j'ai besoin d'avoir une idee generale), que cette table soit reliée à une table parente par une clé étrangère ou pas .

Par ailleurs, il est conseillé de desactiver une contrainte lors du chargement d'une grande quantité de données , ou lors de l'execution de batch, ou lors d'un import de données,car cela permet des gains de performance.

Est ce du fait, par exemple, que dans le cas d'une contrainte de primary key, un index associé est crée et que cela demande des ressources et prend donc plus de temps.
Ainsi, si on desactive la contrainte, on évite la création d'index et on gagne du temps et donc en performance.

Y-a t-il d'autres raisons que celle -ci?

Merci beaucoup de votre aide sur ce point.

Pour finir, je lis que lors de l'activation d'une contrainte, si une ligne transgresse la contrainte, l'ordre échoue et la contrainte reste desactivée.

Ne faut il pas plutôt comprendre que la contrainte reste activée?

Merci encore beaucoup de votre aide.

Cordialement.
Nathalie

[pifor]

Bonjour,

J'ai repris vos explications .
Voici mes remarques

Vous dites ceci


Vous ajoutez ceci


C'est donc qu'une requête insert, update ou delete sur une table enfant provoque également un verrou de niveau table,qui empeche par exemple la table concernée par la requete d'être détruite par un DROP TABLE.
Oui ou Non( j'ai besoin d'une réponse)?

Peut on donc affirmer qu'il y a bien les deux niveaux de verrouillage qui s'appliquent à une table lorsqu'elle est l'objet de requete de type insert, update ou delete.

Oui: au niveau table et au niveau ligne.

Par ailleurs, j'ai lu le document en lien ( precedent mail).
D'après ce que j'ai compris, pour les requêtes de type insert, delete et update, le type de verrouilage est row exclusive (rx).
Si j'ai bien compris, ce verrouillage permet d'autres requêtes de type update, insert et delete sur la table concernée mais pas pour les mêmes lignes, elle interdit également un verrouillage qui ne permettrait qu' une lecture seule de la table ou une ecriture seule sur la table.( explications données dans la partie :row exclusive table locks).

Ensuite , dans le paragraphe "default locking for insert, update delete and select for update" il est dit que la transaction qui contient une requête de ce type acquiert le type de verrou row exclusive sur les lignes modifiées.D'autres transactions ne peuvent pas mettre à jour ou supprimer les lignes verrouillées jusqu'à la fin de la requête, ce qui rejoint ce qui a ete dit dans la partie explicatiove de rox exclusive table locks.

Donc est ce là ce que je dois retenir de manière générale concernant le verrouillage d'une table pendant une requete de type insert, delete ou update( j'ai besoin d'avoir une idee generale), que cette table soit reliée à une table parente par une clé étrangère ou pas .

Pour une idée générale, oui

Par ailleurs, il est conseillé de desactiver une contrainte lors du chargement d'une grande quantité de données , ou lors de l'execution de batch, ou lors d'un import de données,car cela permet des gains de performance.

Est ce du fait, par exemple, que dans le cas d'une contrainte de primary key, un index associé est crée et que cela demande des ressources et prend donc plus de temps.
Ainsi, si on desactive la contrainte, on évite la création d'index et on gagne du temps et donc en performance.

Y-a t-il d'autres raisons que celle -ci?

On peut envisager de désactiver des contraintes pour certains traitements à condition de ne pas oublier de les réactiver en fin de traitement. Mais ce devrait plutôt être l'exception que la norme. Je ne conseille pas de désactiver les contraintes pendant des traitements applicatifs: c'est risqué car des erreurs peuvent être masquées et être plus difficiles à analyser en fin de traitement.

Il peut être plus rapide en effet de créer un index en fin de traitement plutôt que de le construire progressivement. Si on veut être sûr de ceci pour un système/environnement particulier, il faut le tester dans le bon environnement le plus représentatif du système de production.

Pour finir, je lis que lors de l'activation d'une contrainte, si une ligne transgresse la contrainte, l'ordre échoue et la contrainte reste desactivée.

Ne faut il pas plutôt comprendre que la contrainte reste activée?

Non, si la contrainte ne peut pas être respectée, elle va rester désactivée.

Exemple:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
SQL> create table t(x int constraint ck check (x > 0));
 
Table created.
 
SQL> insert into t values(1);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> alter table t disable constraint ck;
 
Table altered.
 
SQL> select constraint_name, status from user_constraints where constraint_name='CK';
 
CONSTRAINT_NAME                STATUS
------------------------------ --------
CK                             DISABLED
 
SQL> insert into t values(0);
 
1 row created.
 
SQL> commit;
 
Commit complete.
 
SQL> select constraint_name, status from user_constraints where constraint_name='CK';
 
CONSTRAINT_NAME                STATUS
------------------------------ --------
CK                             DISABLED
 
SQL> alter table t enable constraint ck;
alter table t enable constraint ck
                                *
ERROR at line 1:
ORA-02293: cannot validate (TEST.CK) - check constraint violated
 
 
SQL> select constraint_name, status from user_constraints where constraint_name='CK';
 
CONSTRAINT_NAME                STATUS
------------------------------ --------
CK                             DISABLED
 
SQL>