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,