Index et total dup

**calou_33** · 04/02/2016, 18h29

Bonjour,

J'ai un index qui comprends les 3 champs suivants :
Champ1 Champ2 Champ3
OYFF T5 09/02/2016 09:00:00
OYFF T3 09/02/2016 09:00:00
OYFF T2 09/02/2016 09:00:00
OYFF T4 09/02/2016 09:00:00
OYFF T1 09/02/2016 09:00:00
2CEVV T3 09/02/2016 09:00:00
1VINC T4 09/02/2016 09:00:00
CEHOC T2 09/02/2016 09:00:00
BEVR T2 09/02/2016 09:00:00
1VINC T2 09/02/2016 09:00:00
BEVR T1 09/02/2016 09:00:00
CEHOC T1 09/02/2016 09:00:00
ARFO T5 09/02/2016 09:00:00
2CEVf T4 09/02/2016 09:00:00
2CEVf T1 09/02/2016 09:00:00
ARFO T3 09/02/2016 09:00:00
ASTC T2 09/02/2016 09:00:00
ASTC T4 09/02/2016 09:00:00
ADFDS T4 09/02/2016 09:00:00
2CEVV T4 09/02/2016 09:00:00
2CEVf T5 09/02/2016 09:00:00
ADFDS T2 09/02/2016 09:00:00
2CEVV T1 09/02/2016 09:00:00

L'ensemble de ces 3 champs est normalement unique et pourtant j'ai un total dup (qui représente le nombre de clés dupliqués?) qui est proche de keys.
J'ai : Depth Keys Key Len Max Dup Total Dup Uniques Size, mb AvgFill
3 22820364 0.22 19 21350966 1469398 131.05 100
Merci

**Artemus24** · 05/02/2016, 11h31

Salut Calou_33.

J'ai repris ton exemple et j'ai mis des statistiques afin de voir ce que cela donne :

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
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
CREATE DATABASE '..\Data\Base.fdb' page_size 4096 DEFAULT CHARACTER SET ISO8859_1;
 
set stats on;
 
-- ===========================
-- Création de la table 'test'
-- ===========================
 
create table test (
champ1 char(05)  NOT NULL collate fr_fr,
champ2 char(02)  NOT NULL collate fr_fr,
champ3 timestamp NOT NULL
);
Current memory = 3181168
Delta memory = 565176
Max memory = 3227880
Elapsed time= 0.023 sec
Buffers = 256
Reads = 71
Writes = 43
Fetches = 774
 
-- =========================
-- Création Index sur 'test'
-- =========================
 
create unique index idx on test (champ1,champ2,champ3);
Current memory = 3410432
Delta memory = 227200
Max memory = 3523448
Elapsed time= 0.009 sec
Buffers = 256
Reads = 7
Writes = 13
Fetches = 318
 
-- =====================
-- Insertion dans 'test'
-- =====================
 
insert into test (champ1,champ2,champ3) values ('OYFF',  'T5', '09/02/2016 09:00:00');
Current memory = 3526016
Delta memory = 113520
Max memory = 3596960
Elapsed time= 0.002 sec
Buffers = 256
Reads = 2
Writes = 0
Fetches = 73
insert into test (champ1,champ2,champ3) values ('OYFF',  'T3', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 472
Max memory = 3596960
Elapsed time= 0.000 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('OYFF',  'T2', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.000 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('OYFF',  'T4', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('OYFF',  'T1', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('2CEVV', 'T3', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('1VINC', 'T4', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('CEHOC', 'T2', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('BEVR',  'T2', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('1VINC', 'T2', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('BEVR',  'T1', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('CEHOC', 'T1', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('ARFO',  'T5', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.003 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('2CEVf', 'T4', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.000 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('2CEVf', 'T1', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.004 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('ARFO',  'T3', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.000 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('ASTC',  'T2', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('ASTC',  'T4', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('ADFDS', 'T4', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.000 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('2CEVV', 'T4', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.002 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('2CEVf', 'T5', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.001 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('ADFDS', 'T2', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.000 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
insert into test (champ1,champ2,champ3) values ('2CEVV', 'T1', '09/02/2016 09:00:00');
Current memory = 3526488
Delta memory = 0
Max memory = 3596960
Elapsed time= 0.000 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 6
 
-- ================
-- Vidage de 'test'
-- ================
 
select * from test;
 
CHAMP1 CHAMP2                    CHAMP3
====== ====== =========================
OYFF   T5     2016-09-02 09:00:00.0000
OYFF   T3     2016-09-02 09:00:00.0000
OYFF   T2     2016-09-02 09:00:00.0000
OYFF   T4     2016-09-02 09:00:00.0000
OYFF   T1     2016-09-02 09:00:00.0000
2CEVV  T3     2016-09-02 09:00:00.0000
1VINC  T4     2016-09-02 09:00:00.0000
CEHOC  T2     2016-09-02 09:00:00.0000
BEVR   T2     2016-09-02 09:00:00.0000
1VINC  T2     2016-09-02 09:00:00.0000
BEVR   T1     2016-09-02 09:00:00.0000
CEHOC  T1     2016-09-02 09:00:00.0000
ARFO   T5     2016-09-02 09:00:00.0000
2CEVf  T4     2016-09-02 09:00:00.0000
2CEVf  T1     2016-09-02 09:00:00.0000
ARFO   T3     2016-09-02 09:00:00.0000
ASTC   T2     2016-09-02 09:00:00.0000
ASTC   T4     2016-09-02 09:00:00.0000
ADFDS  T4     2016-09-02 09:00:00.0000
2CEVV  T4     2016-09-02 09:00:00.0000
 
CHAMP1 CHAMP2                    CHAMP3
====== ====== =========================
2CEVf  T5     2016-09-02 09:00:00.0000
ADFDS  T2     2016-09-02 09:00:00.0000
2CEVV  T1     2016-09-02 09:00:00.0000
 
Current memory = 3530480
Delta memory = 3992
Max memory = 3596960
Elapsed time= 0.119 sec
Buffers = 256
Reads = 0
Writes = 0
Fetches = 51
 
-- ====
-- Show
-- ====
 
Show table   test;
CHAMP1                          CHAR(5) CHARACTER SET ISO8859_1 Not Null
                                 COLLATE FR_FR
CHAMP2                          CHAR(2) CHARACTER SET ISO8859_1 Not Null
                                 COLLATE FR_FR
CHAMP3                          TIMESTAMP Not Null
Show Indexes test;
IDX UNIQUE INDEX ON TEST(CHAMP1, CHAMP2, CHAMP3)
 
exit;
 
Database "E:\23.FIREBIRD\99.EXERCICES\DATA\BASE.FDB"
Database header page information:
        Flags                   0
        Generation              10
        System Change Number    0
        Page size               4096
        ODS version             12.0
        Oldest transaction      1
        Oldest active           2
        Oldest snapshot         2
        Next transaction        7
        Sequence number         0
        Next attachment ID      1
        Implementation          HW=AMD/Intel/x64 little-endian OS=Windows CC=MSVC
        Shadow count            0
        Page buffers            0
        Next header page        0
        Database dialect        3
        Creation date           Feb 5, 2016 11:29:39
        Attributes              force write
 
    Variable header data:
        *END*
 
 
Database file sequence:
File E:\23.FIREBIRD\99.EXERCICES\DATA\BASE.FDB is the only file
 
Analyzing database pages ...
TEST (128)
    Primary pointer page: 224, Index root page: 225
    Total formats: 1, used formats: 1
    Average record length: 23.00, total records: 23
    Average version length: 0.00, total versions: 0, max versions: 0
    Average fragment length: 0.00, total fragments: 0, max fragments: 0
    Average unpacked length: 24.00, compression ratio: 1.04
    Pointer pages: 1, data page slots: 1
    Data pages: 1, average fill: 23%
    Primary pages: 1, secondary pages: 0, swept pages: 0
    Empty pages: 0, full pages: 0
    Fill distribution:
         0 - 19% = 0
        20 - 39% = 1
        40 - 59% = 0
        60 - 79% = 0
        80 - 99% = 0
 
    Index IDX (0)
        Root page: 228, depth: 1, leaf buckets: 1, nodes: 23
        Average node length: 25.70, total dup: 0, max dup: 0
        Average key length: 24.65, compression ratio: 1.35
        Average prefix length: 11.65, average data length: 21.70
        Clustering factor: 1, ratio: 0.04
        Fill distribution:
             0 - 19% = 1
            20 - 39% = 0
            40 - 59% = 0
            60 - 79% = 0
            80 - 99% = 0
 
 
Appuyez sur une touche pour continuer...

Dans la partie statistique sur l'index idx, dup = 0.
Il y a bien un index sur ces trois colonnes et il est unique.

Peut-on savoir comment as-tu déclaré ta table et ton index ?

@+

**calou_33** · 05/02/2016, 14h51

Merci beaucoup!
Je n'étais pas sûr de ma compréhension de total dup. Du coup j'ai bien regardé la table et ca m'a permis de voir qu'il y avait un bug! En effet des valeurs sont dupliquées

Index et total dup

Administration Firebird

Vue hybride

Discussions similaires

Partager

Partager