Bonjour,

J'aurais besoin d'un petit coup de main pour optimiser ma requête qui prend plus de jours à s'exécuter.

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
MERGE
        INTO    PDD01400B.DD01405_COMPETENCE dest
            USING   (
                SELECT cpt.rowid AS RID,mig1.new_code_ogr FROM PDD01400B.DD01405_COMPETENCE cpt inner join
                PDD01400B.mig_competences_4_0 mig1 on mig1.old_code_ogr = cpt.code
                where
                    mig1.action = 'R'
            ) src
            ON      (dest.rowid = src.rid)
            WHEN MATCHED THEN UPDATE
                SET dest.code = new_code_ogr;
 
 
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3731241056
 
-----------------------------------------------------------------------------------------------------
| Id  | Operation             | Name                 | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | MERGE STATEMENT       |                      |    80M|  2304M|       |  4033K  (1)| 00:02:38 |
|   1 |  MERGE                | DD01405_COMPETENCE   |       |       |       |            |          |
|   2 |   VIEW                |                      |       |       |       |            |          |
|*  3 |    HASH JOIN          |                      |    80M|  8450M|  3610M|  4033K  (1)| 00:02:38 |
|*  4 |     HASH JOIN         |                      |    80M|  2688M|       |   816K  (1)| 00:00:32 |
|*  5 |      TABLE ACCESS FULL| MIG_COMPETENCES_4_0  |  2247 | 35952 |       |     8   (0)| 00:00:01 |
|*  6 |      TABLE ACCESS FULL| DD01405_COMPETENCE   |   507M|  9204M|       |   815K  (1)| 00:00:32 |
|   7 |     TABLE ACCESS FULL | DD01405_COMPETENCE   |   539M|    37G|       |   818K  (1)| 00:00:32 |
-----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEST".ROWID="CPT".ROWID)
   4 - access("MIG1"."OLD_CODE_OGR"="CPT"."CODE")
   5 - filter("MIG1"."ACTION"='R')
   6 - filter("CPT"."CODE" IS NOT NULL)

Merci pour votre aide