Rafraichissement d'une grande quantité de données

**cyclone_yas** · 16/07/2013, 17h22

Salut à tous.
Je suis sur la version 11G

je dipose d'une table

Code :

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

CNF (col1, col2, col3)

contenant des MILLIONS de lignes.

Les données de la table doivent être màj quotidiennement et au fil de l'eau
Je recois les données dans une table dont la structure est identique

Code :

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

CNF_tmp (col1, col2, col3)

Je précise que env 97% des données recus sont identiques

Voilà ma question :
Comment remplacer le contenu de la table CNF par celui de CNF_tmp sans interruption de service ?
et d'une facon intelligeante ?

je préfère avoir vos avis avant d'implémenter
1000 mercis

**al1_24** · 16/07/2013, 17h31

De quel type de modifications s'agit-il ?
Des ajouts, des modifications, des suppressions ?

**cyclone_yas** · 16/07/2013, 17h42

Merci pour ta réponse Alain,

la clef de la table est (col1, col2, col3)
à chaque reception de la table CNF_tmp il faudra remplacer littéralement le contenu de CNF par celui de CNF_tmp

il faudra faire deux opérations :
1) supprimer les lignes de CNF qui n'y sont plus dans CNF_tmp
2) ajouter les nouvelles lignes qui sont dans CNF_tmp et pas encore dans CNF

Merci

**al1_24** · 16/07/2013, 18h27

Est-ce que la commande MERGE ne répondrait pas à ton besoin ?

**cyclone_yas** · 16/07/2013, 19h14

Merci,
La fonction MERGE aurait été une excellente solution si elle permettait de supprimer les lignes de la table dans la quelle je ferai le merge

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
create table CNF (usr number(3), appli varchar2(5), cmp varchar2(5));
create table CNF_tmp (usr number(3), appli varchar2(5), cmp varchar2(5));
 
insert into CNF (usr, appli, cmp) values (1, 'APP01', 'PTF01');
insert into CNF (usr, appli, cmp) values (2, 'APP01', 'PTF01');
insert into CNF (usr, appli, cmp) values (4, 'APP01', 'PTF08');
insert into CNF (usr, appli, cmp) values (5, 'APP01', 'PTF05');
 
insert into CNF_tmp (usr, appli, cmp) values (1, 'APP01', 'PTF01');
insert into CNF_tmp (usr, appli, cmp) values (2, 'APP01', 'PTF01');
insert into CNF_tmp (usr, appli, cmp) values (6, 'APP01', 'PTF06');
insert into CNF_tmp (usr, appli, cmp) values (7, 'APP01', 'PTF01');

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
 
select * from CNF;
       USR APPLI CMP 
---------- ----- -----
         1 APP01 PTF01 
         2 APP01 PTF01 
         4 APP01 PTF08 
         5 APP01 PTF05 
 
select * from CNF_tmp;
       USR APPLI CMP 
---------- ----- -----
         1 APP01 PTF01 
         2 APP01 PTF01 
         6 APP01 PTF06 
         7 APP01 PTF01

Je fais :

Code :

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

1
2
3
4
5
6
 
MERGE INTO CNF CNF
   USING CNF_tmp TMP
   ON (CNF.usr = TMP.usr and CNF.appli = TMP.appli and CNF.cmp = TMP.cmp)
   WHEN NOT MATCHED THEN INSERT (CNF.usr, CNF.appli, CNF.cmp)
     VALUES (TMP.usr, TMP.appli, TMP.cmp);

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
select * from CNF;
 
       USR APPLI CMP 
---------- ----- -----
         1 APP01 PTF01 
         2 APP01 PTF01 
         4 APP01 PTF08 
         5 APP01 PTF05 
         6 APP01 PTF06 
         7 APP01 PTF01

L'idée est de ne pas avoir dans le résultat les lignes :

Code :

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

1
2
3
4
5
 
       USR APPLI CMP 
---------- ----- -----
         4 APP01 PTF08 
         5 APP01 PTF05

**cyclone_yas** · 17/07/2013, 02h12

Bonsoir,

Il y a aussi la possibilité de :
renommer la table CNF en CNF_OLD
et CNF_tmp en CNF

dans ce cas il faut faire un lock sur la table CNF, interrompre les traitements en cours, ensuite procéder au renomage.

J'aimerai à tout prix éviter de faire un delete sur la table CNF de la différence. Il s'agit de millions de lignes celà peut s'avérer très lent et très gourmand en terme de ressources

Merci beaucoup pour vos avis et votre aide

**pacmann** · 17/07/2013, 09h41

Salut,

Mais la différence est de 3%, non ? Ca fait des millions de lignes ?
La table CNF est-elle modifiée pendant la journée ? Comment est alimentée la table cnf_tmp ? Les tables sont elles indexées ?

**cyclone_yas** · 17/07/2013, 09h51

Bonjour,

c'est la table CNF_tmp qui est alimentée une fois par jour. son contenu doit être déversé dans CNF.

Oui la table CNF est indexé sur sa clé, qui est col1, col2, col3

Merci

**mnitu** · 17/07/2013, 12h00

Envoyé par cyclone_yas

...La fonction MERGE aurait été une excellente solution si elle permettait de supprimer les lignes de la table dans la quelle je ferai le merge
...

Regardez DELETE where_clause dans merge_update_clause

**pacmann** · 17/07/2013, 12h11

Par exemple comme ça ?

Code :

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

1
2
3
4
5
6
7
8
9
10
 
merge into cnf a
using (
  select b.rowid bid, c.rowid cid, usr, appli, cmp
  from cnf b
    natural full outer join cnf_tmp c
  where b.rowid is null or c.rowid is null) d
on (a.rowid = d.bid)
when not matched then insert(a.usr, a.appli, a.cmp) values (d.usr, d.appli, d.cmp)
when matched then update set a.usr = a.usr delete where 1 = 1;

**skuatamad** · 17/07/2013, 13h24

Le DELETE dans le MERGE ne fonctionnera pas, tiré du lien vers la doc proposé par mnitu :

The only rows affected by this clause are those rows in the destination table that are updated by the merge operation.
...
If a row of the destination table meets the DELETE condition but is not included in the join defined by the ON clause, then it is not deleted.

Ici il faut supprimer les lignes qui ne sont plus présentent dans la table source.
Il faut donc faire un DELETE WHERE NOT EXISTS puis un MERGE.

**mnitu** · 17/07/2013, 14h34

Envoyé par skuatamad

Ici il faut supprimer les lignes qui ne sont plus présentent dans la table source.

Merci skuatamad, c'est bien ça qu'il dit et donc ça ne marchera pas! En gros il veut que la table CNF_TMP devient CNF.
Est-ce que ça se fait une seule fois par jour ou N fois ?

**pacmann** · 17/07/2013, 14h46

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
 
SQL> CREATE TABLE CNF (usr number(3), appli varchar2(5), cmp varchar2(5));
 
Table created.
 
Elapsed: 00:00:00.01
SQL> CREATE TABLE CNF_tmp (usr number(3), appli varchar2(5), cmp varchar2(5));
 
Table created.
 
Elapsed: 00:00:00.01
SQL>
SQL> INSERT INTO CNF (usr, appli, cmp) VALUES (1, 'APP01', 'PTF01');
 
1 row created.
 
Elapsed: 00:00:00.00
SQL> INSERT INTO CNF (usr, appli, cmp) VALUES (2, 'APP01', 'PTF01');
 
1 row created.
 
Elapsed: 00:00:00.00
SQL> INSERT INTO CNF (usr, appli, cmp) VALUES (4, 'APP01', 'PTF08');
 
1 row created.
 
Elapsed: 00:00:00.01
SQL> INSERT INTO CNF (usr, appli, cmp) VALUES (5, 'APP01', 'PTF05');
 
1 row created.
 
Elapsed: 00:00:00.00
SQL>
SQL> INSERT INTO CNF_tmp (usr, appli, cmp) VALUES (1, 'APP01', 'PTF01');
 
1 row created.
 
Elapsed: 00:00:00.01
SQL> INSERT INTO CNF_tmp (usr, appli, cmp) VALUES (2, 'APP01', 'PTF01');
 
1 row created.
 
Elapsed: 00:00:00.00
SQL> INSERT INTO CNF_tmp (usr, appli, cmp) VALUES (6, 'APP01', 'PTF06');
 
1 row created.
 
Elapsed: 00:00:00.00
SQL> INSERT INTO CNF_tmp (usr, appli, cmp) VALUES (7, 'APP01', 'PTF01');
 
1 row created.
 
Elapsed: 00:00:00.01
SQL> commit;
 
Commit complete.
 
SQL> merge INTO cnf a
  2  USING (
  3    SELECT b.rowid bid, c.rowid cid, usr, appli, cmp
  4    FROM cnf b
  5      NATURAL full OUTER JOIN cnf_tmp c
  6    WHERE b.rowid IS NULL OR c.rowid IS NULL) d
  7  ON (a.rowid = d.bid)
  8  when NOT matched then INSERT(a.usr, a.appli, a.cmp) VALUES (d.usr, d.appli, d.cmp)
  9  when matched then UPDATE SET a.usr = a.usr DELETE WHERE 1 = 1;
 
4 rows merged.
 
Elapsed: 00:00:00.04
SQL> select * from cnf;
 
       USR APPLI CMP
---------- ----- -----
         1 APP01 PTF01
         2 APP01 PTF01
         7 APP01 PTF01
         6 APP01 PTF06

(Avouez que vous ne l'avez pas lue, ma requête...)

**mnitu** · 17/07/2013, 15h41

Envoyé par pacmann

...(Avouez que vous ne l'avez pas lue, ma requête...)

J’avoue tout de suite

mais je ne sais pas si ça change grande chose parce que cette solution peut s’avérer moins intéressante sur une table de quelques millions des enregistrements.

**pacmann** · 17/07/2013, 16h31

Boh, je suis pas certain que ça soit moins performant...
Avec la boule de cristal, je dirais que ça doit revenir au même

La requête merge va constituer le diff avec left outer join union all right outer join, puis faire le merge. Dans delete puis merge, tu fais également deux fois la jointure.

Du coup, peut être coder en PL un sort-merge manuel en parcourant les deux curseurs en même temps pourrait être meilleur, surtout si les deux tables sont en IOT

**mnitu** · 17/07/2013, 17h41

Très probablement.
Mais si je devrais faire cette opération une seule fois par jour je me suis dit que j’envisagerais des autres solutions comme avoir un synonyme qui pourrait pointer sur la « bonne table » de quelle serait disponible ou bien à des autres solutions moins exotiques.

**skuatamad** · 18/07/2013, 10h05

Envoyé par pacmann

(Avouez que vous ne l'avez pas lue, ma requête...)

J'avais lu MERGE et DELETE mais pas ce qu'il y avait au milieu

**cyclone_yas** · 18/07/2013, 10h22

1000 mercis à vous tous

Effectivement, ne pouvant pas créer d'objet PL, la solution de Pacmann est bonne, la proposition de Mnitu est à étudier aussi.

Encore une fois Merci

**pacmann** · 18/07/2013, 11h10

Attention, la requête telle qu'elle n'est pas correcte si les champs sont nullables. (Planqué dans le natural, les conditions de jointure NULL = NULL ne matchent pas). Il faut dans ce cas préciser les conditions de jointure dans une clause ON en y traitant les cas de double nullité.

Je me suis fait piéger hier comme un bleu, j'en ai fait un article du coup

http://pacmann.over-blog.com/article...119128763.html

@skuat : c'est même pas en diagonal, ça

Rafraichissement d'une grande quantité de données

SQL Oracle

Discussions similaires

Partager

Partager