Discussion :

[Philomelo]

Bonjour,

J'aimerai faire la requête suivante, mais elle ne me semble pas optimisée :

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
create or replace procedure Purge_Histo as
 
BEGIN
 
    DECLARE 
 
        CURSOR id_cust Is
            select cust.customer_number,nvl(cust.fk_household_id,0) fk_household_id, nvl(cust.fk_legalentity_id,0) fk_legalentity_id from customer cust
            where cust.customer_number between 0 and 500000;
        c_limit integer := 1000;
        TYPE id_cust_type IS TABLE OF id_cust%ROWTYPE INDEX BY BINARY_INTEGER;
        id_cust_array id_cust_type; --157540 ms 
        --id_cust_array customer%rowtype;
 
    BEGIN 
 
        FOR IDS IN id_cust LOOP 
 
            --FETCH id_cust BULK COLLECT INTO id_cust_array LIMIT c_limit;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                    select cust.customer_number, cust.update_date, 'CUSTOMER',cust.customer_number, 'SCR' 
                      from customer cust
                     where cust.customer_number = IDS.customer_number;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                    select ah.fk_customer_id,nvl(ah.business_date,ah.creation_date), 'HISTORIC', ah.action_history_id, 'SCR'
                                  from action_history ah
                                 where ah.fk_customer_id = IDS.customer_number;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, ad.update_date, 'ADDRESS', ad.address_id, 'SCR'
                                  from address ad
                                 where ad.fk_household_id = IDS.fk_HOUSEHOLD_ID;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, ad.update_date, 'ADDRESS', ad.address_id, 'SCR'
                                  from address ad
                                 where ad.FK_LEGALENTITY_ID = IDS.FK_LEGALENTITY_ID;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, hh.update_date, 'HOUSEHOLD', hh.household_id, 'SCR'
                                  from household hh
                                 where hh.household_id = IDS.fk_HOUSEHOLD_ID;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.CUSTOMER_NUMBER, comm.update_date, 'COMMUNICATION', comm.communication_id, 'SCR'
                                  from communication comm
                                 where comm.fk_naturalperson_id = IDS.CUSTOMER_NUMBER;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, ei.update_date, 'EXTERNAL_ID', ei.external_identifier_id, 'SCR'
                                  from external_identifier ei
                                 where ei.fk_customer_id = IDS.customer_number;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, ll.update_date, 'LEISURE', ll.LEISURE_ID, 'SCR'
                                  from leisure ll
                                 where ll.FK_NATURALPERSONCUSTOMER_ID = IDS.customer_number;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, np.update_date, 'NOTEPAD', np.NOTEPAD_ID, 'SCR'
                                  from NOTEPAD np
                                 where np.FK_CUSTOMER_ID = IDS.customer_number;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
                                  from OPTIN opt
                                 where opt.fk_customer_id = IDS.customer_number;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
                                  from OPTIN opt
                                  join communication comm on comm.communication_id = opt.fk_communication_id
                                 where comm.fk_naturalperson_id = IDS.customer_number;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
                                  from OPTIN opt
                                  join address ad on ad.address_id = opt.fk_address_id
                                 where ad.fk_household_id = IDS.fk_HOUSEHOLD_ID;
 
            INSERT INTO PURGE_HISTO_DC ("CUSTOMER_NUMBER","CREATION_DATE","EVENT_CONSENT","EVENT_CONSENT_NB","APPLICATION")
                                select IDS.customer_number, opt.update_date, 'OPTIN', opt.OPTIN_ID, 'SCR'
                                  from OPTIN opt
                                  join address ad on ad.address_id = opt.fk_address_id
                                 where ad.FK_LEGALENTITY_ID = IDS.FK_LEGALENTITY_ID;
 
        END LOOP IDS;
 
    end;
 
end;

Est-ce qu'il existe des optimisations possible sur ce type de requêtes ?

Merci pour votre aide,

[McM]

Ne connaissant pas le modèle de données, c'est un peu dur de répondre complètement.

1/ le premier INSERT, tu refais un select sur CUSTOMER alors que ton curseur est déjà sur Customer
=> Récupérer les données dans le Curseur et Remplacer le INSERT SELECT par un INSERT VALUES

2/ Pour tous les INSERT qui ne vont traiter qu'une ligne max, tu peux rajouter la jointure dans le Curseur et faire un INSERT VALUES
Exemple si ADDRESS.fk_household_id est unique, alors c'est possible de le rajouter dans le curseur et de faire un INSERT VALUES s'il y a une donnée.

3/ Tu peux aussi remplacer le curseur par une jointure sur CUSTOMER pour chaque INSERT. A vérifier au niveau explain plan et temps de réponse.

[Michel.Priori]

Pour commencer il est drôle de lire le nom de la procédure pour constater qu'elle ne contient que des INSERT.

Ensuite, dans un premier temps, je ne conserverais que le curseur dans la partie DECLARE, le reste est du code mort.

Et pour finir pouvez-vous expliquer en quoi curseur est utile ?