Discussion :

[Daily]

Bonjour,

Désolé si ma question est idiote.
J'ai utilisé la recherche avec les mots «index» et «primary» et je n'ai pas trouvé ce que je voulais.

La situation: J'ai une table énorme avec un index unique, des index dupliqués
et une primary key.

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
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
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
 
CREATE TABLE VENTCLI
(
  CMAG_VENTCLI        CHAR(5)                   NOT NULL,
  CPRP_VENTCLI        CHAR(6)                   NOT NULL,
  DATE_VENTCLI        DATE                      NOT NULL,
  QTE_VENDU_VENTCLI   NUMBER(8,3),
  TVA_VENTCLI         NUMBER(5,2),
  PRIX_ACHAT_VENTCLI  NUMBER(8,2),
  PRIX_VENTE_VENTCLI  NUMBER(8,2),
  CA_VENTCLI          NUMBER(10,2)
)
TABLESPACE USERS
PCTUSED    40
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCACHE
NOPARALLEL;
 
 
CREATE INDEX IND1_VENTCLI ON VENTCLI
(CMAG_VENTCLI)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX IND2_VENTCLI ON VENTCLI
(CPRP_VENTCLI)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE INDEX IND3_VENTCLI ON VENTCLI
(DATE_VENTCLI)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
CREATE UNIQUE INDEX PK_VENTCLI ON VENTCLI
(CMAG_VENTCLI, CPRP_VENTCLI, DATE_VENTCLI)
LOGGING
TABLESPACE INDX
PCTFREE    10
INITRANS   2
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            MINEXTENTS       1
            MAXEXTENTS       2147483645
            PCTINCREASE      0
            FREELISTS        1
            FREELIST GROUPS  1
            BUFFER_POOL      DEFAULT
           )
NOPARALLEL;
 
 
ALTER TABLE VENTCLI ADD (
  CONSTRAINT PK_VENTCLI PRIMARY KEY (CMAG_VENTCLI, CPRP_VENTCLI, DATE_VENTCLI)
    USING INDEX 
    TABLESPACE INDX
    PCTFREE    10
    INITRANS   2
    MAXTRANS   255
    STORAGE    (
                INITIAL          64K
                MINEXTENTS       1
                MAXEXTENTS       2147483645
                PCTINCREASE      0
                FREELISTS        1
                FREELIST GROUPS  1
               ));

Le problème: Je voudrais gagner de la place dans cette base.
Les questions:
- Si on utilise un index unique, à quoi sert la primary key puisque la contrainte d'unicité est assurée par l'index?
- Est-il normal, comme dans mon cas, de donner le même nom d'objet à la primary key et à l'index unique ou est-ce une erreur de design?
- Index dupliqués, sont ils utiles dans mon cas, puisque l'index unique est la concaténation de ceux-ci?
- La question qui résume tout: Si je supprime la primary key et les index dupliqués est-ce que ça marchera beaucoup moins bien?

Merci,

Patrick

[Daily]

Bon, personne ne sait alors???

[13thFloor]

- Si on utilise un index unique, à quoi sert la primary key puisque la contrainte d'unicité est assurée par l'index?
=> un index unique permet de prendre en compte des valeurs nulles
Dans ton cas, la PK suffira.
En plus, les 3 colonnes qui composent l'UK sont NOT NULL => non-sens d'avoir une UK dessus.

- Est-il normal, comme dans mon cas, de donner le même nom d'objet à la primary key et à l'index unique ou est-ce une erreur de design?
=> dans un même schéma, PK et UK auront des noms différents. Les nom d'un même type d'objet sont toujours différents pour un même schéma.

- Index dupliqués, sont ils utiles dans mon cas, puisque l'index unique est la concaténation de ceux-ci?
=> ils peuvent être utiles, notamment ceux sur les colonnes CPRP_VENTCLI et DATE_VENTCLI, si des requêtes ne font pas référence aux première colonne sde la PK dans leur clause where (quoique tu peux ruser en forçant l'utilisation de la PK si tu connais la valeur min ou max du champ CMAG_VENTCLI : dans ce cas du ajoute à la clause where une condition portant sur ce champ .Ex : valeur min = "Toto1", tu ajoutes where CMAG_VENTCLI>" ").

- La question qui résume tout: Si je supprime la primary key et les index dupliqués est-ce que ça marchera beaucoup moins bien?
=> garde la PK : elle a un rôle de contrainte. Les 3 colonnes qui la composent sont not null, donc pas de raison d'avoir une UK.

Si tu veux gagner de la place, fais de temps en temps des coalesce ou shrink de tes index. Et penses à utiliser du VARCHAR au lieu du CHAR.

[Daily]

Bonjour et merci d'avoir pris le temps de me répondre.

=>un index unique permet de prendre en compte des valeurs nulles
Dans ton cas, la PK suffira.
En plus, les 3 colonnes qui composent l'UK sont NOT NULL => non-sens d'avoir une UK dessus.

Bon là j'ai pas tout suivi (je suis un peu con ).
- Pour moi une PK est une contrainte d'intégrité (contrôle de l'unicité) et un index est un objet qui sert a accélérer les accès. Or tu utilises le terme UK (unique key?) pour index unique je suppose. Un index est une clé, c'est pareil???
- Je n'ai pas compris le 'non-sens'

=> dans un même schéma, PK et UK auront des noms différents. Les nom d'un même type d'objet sont toujours différents pour un même schéma.

Je pense comme toi, mais dans mon script l'index unique et la contrainte portent le même nom. Il ne sont pas dans le même schéma?

=>ils peuvent être utiles, notamment ceux sur les colonnes CPRP_VENTCLI et DATE_VENTCLI, si des requêtes ne font pas référence aux première colonne sde la PK dans leur clause where (quoique tu peux ruser en forçant l'utilisation de la PK si tu connais la valeur min ou max du champ CMAG_VENTCLI : dans ce cas du ajoute à la clause where une condition portant sur ce champ .Ex : valeur min = "Toto1", tu ajoutes where CMAG_VENTCLI>" ").

Ok, tu penses qu'ils peuvent servir. Je posais la question car avant Oracle, j'étais DBA sur Oracle-RDB et l'optimiseur se débrouillait bien avec des morceaux d'index quelque soit leur place dans l'index qu'il composait.

=> garde la PK : elle a un rôle de contrainte. Les 3 colonnes qui la composent sont not null, donc pas de raison d'avoir une UK.
Si tu veux gagner de la place, fais de temps en temps des coalesce ou shrink de tes index. Et penses à utiliser du VARCHAR au lieu du CHAR.

- Comme le première réponse, je n'ai pas compris l'amalgame que tu fais entre index unique et cle primaire, une contrainte est un index?
- Merci, je vais voir ça.

Encore merci pour tes réponses.

Patrick

[chrifo]

Bonjour,
Pour répondre à la question que tu te poses, sous ORACLE, la création d'une contrainte PK génère un index.

Remarque : Il me semble que certains SGBD comme MySQL génèrent également des index pour les FK.

[13thFloor]

La PK est une contrainte d'intégrité matérialisée par une structure physique : un index.
Donc double rôle : contrôle de l'unicité et permettre d'accès plus rapide.

Si un champ est de nature non-null, il a déja une contrainte. Créer un index pour respecter cette contrainte fera double emploi. Le seul intérêt sera d'optimiser les accès à la table quand les critères de sélection utilisent le sclés de l'index (tout ou partie des champs, dans l'ordre de création de l'index).

Un index unique permet à la table d'avoir des valeurs nulles.
Un index de clé primaire n'autorise pas les valeurs nulles.

[Daily]

Bonjour,
Pour répondre à la question que tu te poses, sous ORACLE, la création d'une contrainte PK génère un index.

Remarque : Il me semble que certains SGBD comme MySQL génèrent également des index pour les FK.

La PK est une contrainte d'intégrité matérialisée par une structure physique : un index.
Donc double rôle : contrôle de l'unicité et permettre d'accès plus rapide.

Si un champ est de nature non-null, il a déja une contrainte. Créer un index pour respecter cette contrainte fera double emploi. Le seul intérêt sera d'optimiser les accès à la table quand les critères de sélection utilisent le sclés de l'index (tout ou partie des champs, dans l'ordre de création de l'index).

Un index unique permet à la table d'avoir des valeurs nulles.
Un index de clé primaire n'autorise pas les valeurs nulles.

Ok, je comprend mieux, le script que j'ai posté n'est pas celui qui à servi à la création de la table mais celui renvoyé par Toad. Apparemment Toad donne la création de l'index et de la contrainte. En réalité seule la contrainte à du être créée explicitement et l'index l'a été implicitement par Oracle (D'ou le nom identique)

Pour les perfs avec 3 index dupliqués ou un unique, je faire des tests...

Merci à vous

[Wurlitzer]

Une petite remarque supplémentaire.

Comme cela à déja été dis une FK est un index unique avec en plus une contrainte not null.

Une autre différence importante c'est que si tu veux faire une Foreign Key d'une autre table vers ta table tu ne peux le faire que vers la Primary Key de ta table. Dans ce cas un index Unique ne sert a rien.

Autre chose en passant.

Remarque : Il me semble que certains SGBD comme MySQL génèrent également des index pour les FK.

Oracle n'index pas automatiquement les Foreign Key est c'est dommage car il faut toujours le faire. Sinon cela peut poser de gros problème de performances a cause des locks voir des Deadlock