Discussion :

[Lps]

Bonjour,

Il s'agit d'ajouter 3 champs dans une table contenant près de 3 millions d'enregistrements :

Code :

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

1
2
3
4
5
6
7
1> select count(*) from matable
2> go
 
-----------
    2917894
 
(1 row affected)

Quelle méthode utiliser ? Suppression des index de la table, mise à jour, recréation des index ?

Dump de la table, destruction de la table, recréation de la table, peuplement de la table, création des index ?

Solution actuelle :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
drop index matable.monindex
go
 
alter table matable
  add  champ1 numeric(18,0) default 0 not null,
       champ2 numeric(18,0) default 0 not null,
       champ3 numeric(18,0) default 0 not null
go
 
create clustered index monindex on matable (field1, field2, field3, field4)
with
allow_dup_row
go

Cette solution est excessivement longue. Est-elle la plus rapide ?

Merci d'avance

[dbafranck]

Tester si la solution suivante est plus rapide :
- drop de l'index
- bcp out en mode caractère
- rajout (avec vi ou autre éditeur) des champs (les trois 0) directement dans le fichier texte (en respectant séparateur de champs, ...)
- truncate de la table
- alter de la table
- bcp in
- recréation de l'index (si c'est la création de l'index cluster qui est très longue, tester avec un index non cluster si possible).

[Fabien Celaia]

- pourquoi bcp en mode caractère ?

Sinon, essayer cette variante

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
 
declare @initial int, @final int
 
exec  sp_rename VotreTable, VotreTable_old
 
select @initial=count(*) from VotreTable_old
 
select *, 
         convert(numeric(18,0), NULL) as champ1,
         convert(numeric(18,0), NULL) as champ2,
         convert(numeric(18,0), NULL) as champ3
into VotreTable
from VotreTable_old
select @final=@@rowcount
 
if @initial=@final
   BEGIN
   create clustered index monindex on matable (field1, field2, field3, field4)
   drop table VotreTable_Old
   END
else
   print 'Erreur lors de la copie'

Quant à la rationalité d'utiliser un allow_dup_rows sur un index non-unique, je vous laisse juger

[dbafranck]

Oui il y a des chances que cette solution soit elle aussi plus rapide que la première solution du alter table avec 3 millions de lignes. Remplacer nénmoins les trois NULL du convert par trois 0 et penser à rajouter les trois default à 0 sur les trois colonnes rajoutées.

Concernant le bcp out en mode caractère, c'était pour obtenir un fichier directement modifiable, afin de pouvoir rajouter les trois 0 à la fin de chacune des linges (dans le fichier texte ainsi obtenu par le bcp out en mode caractère).

[Lps]

Quant à la rationalité d'utiliser un allow_dup_rows sur un index non-unique, je vous laisse juger

Je dois comprendre que c'est "stupide" :

[Fabien Celaia]

Je ne me permettrai pas !!!
Disons plutôt... inadéquat, non ?

[Lps]

Je me suis mal exprimé, j'ai écris "stupide" et entre guillemets en pensant redondant, superflu...

[Fabien Celaia]

Acquis pour superflu !

Affaire réglée ?

[dbafranck]

Bonjour,
Voici un petit test fort interessant au sujet de l'option ALLOW_DUP_ROW avec un index clustered.
En fait, même si l'index clustered n'est pas unique, il refuse de se créer sur une table comporant des doublons ... sauf si l'on utilise l'option pré-cité.

1> CREATE TABLE t (c int)
2> GO
1> INSERT t VALUES (1)
2> INSERT t VALUES (1)
3> go
(1 row affected)
(1 row affected)
1> CREATE CLUSTERED INDEX i ON t(c)
2> GO
Msg 1508, Level 16, State 2:
Server 'SYBASE', Line 1:
Create index aborted on duplicate rows. Primary key is '1'
1> CREATE CLUSTERED INDEX i ON t(c) WITH ALLOW_DUP_ROW
2> go
1>

[Lps]

Je pense que je vais conserver cette option alors