ALTER TABLE sur une table avec beaucoup de données

Version imprimable

Bonjour,

J'ai une base de données qui est en production, je voudrais faire une mise à jour d'une table avec la commande suivante:

Code:

1
2
3
4
5
6
7
8
9
ALTER TABLE TESTREQ MODIFY (
 PrimarySampleSampleType   DEFAULT 1 NOT NULL NOVALIDATE,
 PrimarySampleSubjectState DEFAULT 1 NOT NULL NOVALIDATE,
 PrimarySampleDilution     DEFAULT 0 NOT NULL NOVALIDATE,
 AnalysisMethod         DEFAULT 1 NOT NULL NOVALIDATE,
 AnalysisPriority       DEFAULT 1 NOT NULL NOVALIDATE,
 AnalysisPerformer      DEFAULT 1 NOT NULL NOVALIDATE,
 AnalysisOrganization   DEFAULT 1 NOT NULL NOVALIDATE
)

Comme la table TESTREQ contient beaucoup de données, l'exécution de la commande va durer plus de 8 heures.

Mon objectif est de réduire ce temps pour ne pas trop perturber la production.

J'ai essayé de diviser la commande comme suit:

1)

Code:

1
2
3
4
5
6
7
8
9
ALTER TABLE TESTREQ ADD (
 PrimarySampleSampleType   NUMBER,
 PrimarySampleSubjectState NUMBER,
 PrimarySampleDilution     NUMBER,
 AnalysisMethod         NUMBER,
 AnalysisPriority       NUMBER,
 AnalysisPerformer      NUMBER,
 AnalysisOrganization   NUMBER
);

Code:

1
2
3
4
5
6
7
8
9
ALTER TABLE TESTREQ MODIFY (
 PrimarySampleSampleType   DEFAULT 1,
 PrimarySampleSubjectState DEFAULT 1,
 PrimarySampleDilution     DEFAULT 0,
 AnalysisMethod         DEFAULT 1 ,
 AnalysisPriority       DEFAULT 1,
 AnalysisPerformer      DEFAULT 1,
 AnalysisOrganization   DEFAULT 1
)

3)
Une application qui va mettre les valeurs par défaut sur les enregistrement existant.

4)

Code:

1
2
3
4
5
6
7
8
9
ALTER TABLE TESTREQ MODIFY (
 PrimarySampleSampleType   	NOT NULL,
 PrimarySampleSubjectState 	NOT NULL,
 PrimarySampleDilution     	NOT NULL,
 AnalysisMethod         	NOT NULL,
 AnalysisPriority       	NOT NULL,
 AnalysisPerformer      	NOT NULL,
 AnalysisOrganization   	NOT NULL
)

Mais ce 4° commande prendre toujours beaucoup de temps.
Est ce qu'il y a possibilité que l'Oracle accepte de changer les contraints mais sans faire la vérification des données ?

Bonjour,

Pourquoi pas

1.

Code:

1
2
3
4
5
6
7
8
9
alter table LA_TABLE add
(
PrimarySampleSampleType number DEFAULT 1 NOT NULL ,
PrimarySampleSubjectState number DEFAULT 1 NOT NULL ,
PrimarySampleDilution number DEFAULT 0 NOT NULL ,
AnalysisMethod number DEFAULT 1 NOT NULL ,
AnalysisPriority number DEFAULT 1 NOT NULL ,
AnalysisPerformer number DEFAULT 1 NOT NULL ,
AnalysisOrganization number DEFAULT 1 NOT NULL );

et 2 Midif applicatif
?

Code:

1
2
3
4
5
6
7
8
9
alter table LA_TABLE add
(
PrimarySampleSampleType number DEFAULT 1 NOT NULL ,
PrimarySampleSubjectState number DEFAULT 1 NOT NULL ,
PrimarySampleDilution number DEFAULT 0 NOT NULL ,
AnalysisMethod number DEFAULT 1 NOT NULL ,
AnalysisPriority number DEFAULT 1 NOT NULL ,
AnalysisPerformer number DEFAULT 1 NOT NULL ,
AnalysisOrganization number DEFAULT 1 NOT NULL );

Oui, ceci est ma solution de départ, mais cela prendre + de 8 heures d'exécution!

16/06/2009, 11h29
NGasparotto

Malheureusement, il est tres couteux d'ajouter/supprimer une colonne, Oracle verifie tous les blocks de la table...
8 heures n'est pas acceptable ? Apres tout, ce n'est pas une operation quotidienne.

Une autre solution a etudier est :
1. creer une table Table_b avec toutes les colonnes que tu veux, celles existantes dans la table de depart table_a, plus les nouvelles avec toutes les caracteristiques.
2. insert de la table source dans la table cible avec /*+ APPEND */
3. drop l'ancienne table table_a
4. rename table_b en table_a

Biensur, il te faudra penser recreer les index, pk, fk, grant...

Nicolas.

Essaiez

Code:

1
2
3
4
 
alter table LA_TABLE 
  add (constraint NN_PrimarySampleSampleType
  check ("PrimarySampleSampleType" IS NOT NULL) novalidate)

Citation:

Envoyé par mnitu

Essaiez

Code:

1
2
3
4
 
alter table LA_TABLE 
  add (constraint NN_PrimarySampleSampleType
  check ("PrimarySampleSampleType" IS NOT NULL) novalidate)

Surtout a eviter si tu ne veux pas creer une grande confusion. Dans ce cas, on ne pourra inserer NULL certes, mais la table ne montrera pas NOT NULL sur la colonne concernee... plutot source de confusion...

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> create table mytable(c1 number);
 
Table created.
 
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 
SQL> alter table mytable add (constraint nn_mytable check (c1 is not null) novalidate);
 
Table altered.
 
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 
SQL> insert into mytable values (null);
insert into mytable values (null)
*
ERROR at line 1:
ORA-02290: check constraint (SYSADM.NN_MYTABLE) violated

Nicolas.

16/06/2009, 11h44
mnitu

Citation:

Envoyé par NGasparotto

Surtout a eviter si tu ne veux pas creer une grande confusion. Dans ce cas, on ne pourra inserer NULL certes, mais la table ne montrera pas NOT NULL sur la colonne concernee... plutot source de confusion...
...

C'est vrai Nicolas vous avez raison. Mais l'optimiseur le saura.

Citation:

Envoyé par mnitu

C'est vrai Nicolas vous avez raison. Mais l'optimiseur le saura.

Il s'agit surtout d'une "surcharge" plutot couteuse...

Code:

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
SQL> create table mytable(c1 number);
 
Table created.
 
Elapsed: 00:00:00.00
SQL> alter table mytable add (constraint nn_mytable check (c1 is not null) novalidate);
 
Table altered.
 
Elapsed: 00:00:00.00
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 
SQL> begin
  2  for i in 1..100000 loop
  3  begin
  4  insert into mytable values (null);
  5  exception when others then null;
  6  end;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:18.03
SQL> drop table mytable;
 
Table dropped.
 
Elapsed: 00:00:00.00
SQL> create table mytable(c1 number not null);
 
Table created.
 
Elapsed: 00:00:00.00
SQL> desc mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                        NOT NULL NUMBER
 
SQL> begin
  2  for i in 1..100000 loop
  3  begin
  4  insert into mytable values (null);
  5  exception when others then null;
  6  end;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:08.06
SQL>

Bon, d'accord, c'est un cas extreme (100000 iterations), mais 18 secondes avec CHECK contrainte contre 8 secondes pour le NOT NULL standard, il n'y a pas photo, non ?:mrgreen:

Nicolas.

Citation:

Envoyé par NGasparotto

Il s'agit surtout d'une "surcharge" plutot couteuse...
...

Pas si convaincu

Code:

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
 
Connecté à :
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
JServer Release 9.2.0.5.0 - Production
 
mni@DEV15>CREATE TABLE mytable(c1 number);
 
Table créée.
 
mni@DEV15>ALTER TABLE mytable ADD (constraint nn_mytable CHECK (c1 IS NOT NULL) novalidate);
 
Table modifiée.
 
mni@DEV15>CREATE TABLE mytable_1(c1 number NOT NULL);
 
Table créée.
 
mni@DEV15>desc mytable
 Nom                                                                     NULL ?   Type
 ----------------------------------------------------------------------- -------- --------------------------------
-----------------
 C1                                                                               NUMBER
 
mni@DEV15>desc mytable_1
 Nom                                                                     NULL ?   Type
 ----------------------------------------------------------------------- -------- --------------------------------
-----------------
 C1                                                                      NOT NULL NUMBER
mni@DEV15>begin
  runstats_pkg.rs_start;
  insert into mytable select level from dual connect by level <= 100000;
  runstats_pkg.rs_middle;
  insert into mytable_1 select level from dual connect by level <= 100000;
  runstats_pkg.rs_stop(100);
end;
/
  2    3    4    5    6    7    8
Run1 ran in 65 hsecs
Run2 ran in 113 hsecs
run 1 ran in 57,52% of the time
 
Name                                  Run1        Run2        Diff
LATCH.simulator hash latch              13         148         135
STAT...Elapsed Time                    105         242         137
STAT...redo size                 1,590,616   1,590,412        -204
STAT...session pga memory max    4,784,128           0  -4,784,128
 
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
      14,552      14,744         192     98.70%
 
Procédure PL/SQL terminée avec succès.

[Edit]
Nicolas, désolé, mais j’avais lu un peu trop vite ton exemple.
Je comprends maintenant les différences entre ton exemple et le mienne. La où je ne suis pas d’accord avec ton test est que si la colonne est non nulle dans la plupart des cases, en pratique il y aura des valeurs pour cette colonne.
Ce que je veux dire est que les exceptions sont plutôt exceptionnelles.

[/Edit]

Citation:

Envoyé par mnitu

...La où je ne suis pas d’accord avec ton test est que si la colonne est non nulle dans la plupart des cases, en pratique il y aura des valeurs pour cette colonne.
Ce que je veux dire est que les exceptions sont plutôt exceptionnelles.

Mes tests sont sur Oracle 10.2.0.4
Ok, alors la meme chose avec insertion de 1 null tous les 1000 (semble correct ?):

Code:

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
SQL> CREATE TABLE mytable(c1 number);
 
Table created.
 
Elapsed: 00:00:00.00
SQL> ALTER TABLE mytable ADD (constraint nn_mytable CHECK (c1 IS NOT NULL) novalidate);
 
Table altered.
 
Elapsed: 00:00:00.00
SQL> DESC mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 NUMBER
 
SQL> begin
  2  FOR i IN 1..100000 loop
  3  begin
  4  INSERT INTO mytable VALUES (decode(mod(i,1000),0,null,i));
  5  exception when others then NULL;
  6  end;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:05.03
SQL> DROP TABLE mytable;
 
Table dropped.
 
Elapsed: 00:00:00.01
SQL> CREATE TABLE mytable(c1 number NOT NULL);
 
Table created.
 
Elapsed: 00:00:00.00
SQL> DESC mytable
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                        NOT NULL NUMBER
 
SQL> begin
  2  FOR i IN 1..100000 loop
  3  begin
  4  INSERT INTO mytable VALUES (decode(mod(i,1000),0,null,i));
  5  exception when others then NULL;
  6  end;
  7  end loop;
  8  end;
  9  /
 
PL/SQL procedure successfully completed.
 
Elapsed: 00:00:04.04
SQL>

Bon, de toute facon, le NOT NULL est le standard, pas le CHECK NOT NULL.
Et puis je ne suis pas sur tu aies vraiment prouve que le CHECK NOT NULL est plus rapide parce que :
1. sur une version d'Oracle non supportee
2. avec un CONNECT BY sans PRIOR
;)

Nicolas.

16/06/2009, 15h06
laurentschneider
peut-être me trompé-je, mais la question n'est-elle pas de faire un NOVALIDATE sur le not null?
Code:

1 2 3 4 5 6 7 SQL> create table t(x number); Table created. SQL> alter table t modify (x not null novalidate); Table altered.
?
16/06/2009, 15h43
mnitu
Citation:
Envoyé par laurentschneider

peut-être me trompé-je, mais la question n'est-elle pas de faire un NOVALIDATE sur le not null?

Code:

1 2 3 4 5 6 7 SQL> create table t(x number); Table created. SQL> alter table t modify (x not null novalidate); Table altered.

?
Non Laurent tu ne te trompe pas. Sauf que parfois j’oublie que les choses évoluent.

Citation:

Envoyé par NGasparotto

...
Et puis je ne suis pas sur tu aies vraiment prouve que le CHECK NOT NULL est plus rapide parce que :
1. sur une version d'Oracle non supportee
2. avec un CONNECT BY sans PRIOR
;)

Nicolas.

Bref, apres avoir prise en compte tes remarques:

Code:

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
 
Connected to Oracle Database 10g Express Edition Release 10.2.0.1.0 
Connected as mni
 
SQL> 
SQL> drop table mytable;
 
Table dropped
SQL> drop table mytable_1;
 
Table dropped
SQL> CREATE TABLE mytable(c1 number);
 
Table created
SQL> ALTER TABLE mytable ADD (constraint nn_mytable CHECK (c1 IS NOT NULL) novalidate);
 
Table altered
SQL> CREATE TABLE mytable_1(c1 number NOT NULL);
 
Table created
SQL> CREATE OR REPLACE TYPE number_ntt AS TABLE OF NUMBER;
  2  /
 
Type created
SQL> CREATE or replace FUNCTION row_generator (
  2                    rows_in IN PLS_INTEGER
  3                    ) RETURN number_ntt PIPELINED IS
  4  BEGIN
  5    FOR i IN 1 .. rows_in LOOP
  6      PIPE ROW (i);
  7    END LOOP;
  8    RETURN;
  9  END;
 10  /
 
Function created
SQL> desc mytable
Name Type   Nullable Default Comments 
---- ------ -------- ------- -------- 
C1   NUMBER Y                         
SQL> desc mytable_1
Name Type   Nullable Default Comments 
---- ------ -------- ------- -------- 
C1   NUMBER                           
SQL> set serveroutput on
SQL> begin
  2    runstats_pkg.rs_start;
  3    INSERT INTO mytable_1 SELECT column_value FROM TABLE(row_generator(100000));
  4    runstats_pkg.rs_middle;
  5    INSERT INTO mytable SELECT column_value FROM TABLE(row_generator(100000));
  6    runstats_pkg.rs_stop(1000);
  7  end;
  8  /
 
Run1 ran in 81 hsecs
Run2 ran in 96 hsecs
run 1 ran in 84,38% of the time
 
Name                                  Run1        Run2        Diff
LATCH.row cache objects                929       2,396       1,467
STAT...consistent gets from ca         929       2,618       1,689
STAT...consistent gets                 929       2,618       1,689
LATCH.library cache lock               402       2,353       1,951
STAT...session logical reads         3,551       5,640       2,089
LATCH.library cache pin                637       2,960       2,323
STAT...recursive calls               1,322       4,721       3,399
LATCH.library cache                  1,065       5,388       4,323
LATCH.cache buffers chains          10,935      15,610       4,675
LATCH.session allocation             2,603       9,400       6,797
STAT...undo change vector size     253,208     277,864      24,656
STAT...session pga memory max      262,144     196,608     -65,536
STAT...redo size                 1,625,728   1,695,580      69,852
STAT...physical read bytes          16,384     114,688      98,304
STAT...physical read total byt      16,384     114,688      98,304
STAT...session uga memory max      254,380     130,928    -123,452
STAT...session uga memory          130,928           0    -130,928
STAT...session pga memory          196,608           0    -196,608
 
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
      21,726      46,090      24,364     47.14%
 
PL/SQL procedure successfully completed
 
SQL>

[Edit]
Corrigé le test. Attention à l'ordre des inserts.
[/Edit]

16/06/2009, 17h44
NGasparotto

Cool, jolie demo.
:ccool:

Nicolas.

J'ai refait un test sur ma base 11g

Code:

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
 
C:\Users\Marius>sqlplus mni/mni
 
SQL*Plus: Release 11.1.0.6.0 - Production on Mar. Juin 16 19:08:32 2009
 
Copyright (c) 1982, 2007, Oracle.  All rights reserved.
 
 
Connecté à :
Oracle Database 11g Enterprise Edition Release 11.1.0.6.0 - Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
...
mni@DIANA>
mni@DIANA> SET serveroutput ON
mni@DIANA>
mni@DIANA> begin
  2    runstats_pkg.rs_start;
  3    INSERT INTO mytable_1 SELECT column_value FROM TABLE(row_generator(100000));
  4    runstats_pkg.rs_middle;
  5    INSERT INTO mytable SELECT column_value FROM TABLE(row_generator(100000));
  6    runstats_pkg.rs_stop(1000);
  7  end;
  8  /
Run1 ran in 16 hsecs
Run2 ran in 17 hsecs
run 1 ran in 94,12% of the time
 
Name                                  Run1        Run2        Diff
STAT...session uga memory max      123,452      65,464     -57,988
STAT...session uga memory           65,464           0     -65,464
STAT...session pga memory          131,072      65,536     -65,536
 
Run1 latches total versus runs -- difference and pct
        Run1        Run2        Diff       Pct
      13,298      13,643         345     97.47%
 
Procédure PL/SQL terminée avec succès.
 
mni@DIANA> select table_name, constraint_name, search_condition
  2  from user_constraints
  3  where table_name like 'MYTABLE%'
  4  /
 
TABLE_NAME                     CONSTRAINT_NAME
------------------------------ ------------------------------
SEARCH_CONDITION
--------------------------------------------------------------------------------
MYTABLE                        NN_MYTABLE
c1 IS NOT NULL
 
MYTABLE_1                      SYS_C0012938
"C1" IS NOT NULL

Et j'ai l'impression que c'est la même chose. :lol:

16/06/2009, 22h05
CVPING

Merci pour vos réponses trés démonstrative.:D