Discussion :

[rcb73]

Bonjour,

j'ai un gros problème de performance au niveau d'une de mes requêtes update qui comprend un select à l'interieur du set, au niveau du cout de la requête c une catastrophe mais je ne sais pas comment faire autrement...

voici la requête :

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
UPDATE m2oPointComptage pds
SET m2oDateRaccorde =
  ( SELECT DISTINCT first_value(e.m2oDateDerRx) over (partition BY pds.sysId order by e.sysUpdTime)
  FROM m2oCompteur cpt,
    m2oEquipement eq,
    m2oHrNbRecepCr e
  WHERE e.m2oTraite        = 'N'
  AND e.m2oIdEquipement    = eq.m2oAdresseRadio
  AND e.m2oTypeDeTrame     = '3'
  AND eq.sysClass          = 'm2oModule'
  AND eq.m2oTechno         = 'HR'
  AND cpt.sysId            = eq.m2ocompteur_id
  AND pds.sysId            = cpt.m2opointcomptage_id
  AND e.m2oDateDerRx      IS NOT NULL
  AND pds.m2oDateRaccorde IS NULL
  )
WHERE pds.m2oDateRaccorde IS NULL
AND EXISTS
  (SELECT 1
  FROM m2oCompteur cpt,
    m2oEquipement eq,
    m2oHrNbRecepCr e
  WHERE e.m2oTraite     = 'N'
  AND e.m2oIdEquipement = eq.m2oAdresseRadio
  AND e.m2oTypeDeTrame  = '3'
  AND eq.sysClass       = 'm2oModule'
  AND eq.m2oTechno      = 'HR'
  AND cpt.sysId         = eq.m2ocompteur_id
  AND pds.sysId         = cpt.m2opointcomptage_id
  AND e.m2oDateDerRx   IS NOT NULL
  )

j'ai ajouté cette requête sql et le plan d'exécution fourni par sql developper en pièces jointes.

Pour info les champs sysid sont les clefs primaires de mes tables.
J'ai essayé de créer tous les index possibles mais je pense que je suis arrivé au bout de cette possibilité... :

Code :

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

1
2
3
4
CREATE INDEX m2ohrnbrecepcr_i1 ON M2OHRNBRECEPCR(M2OTRAITE, M2OIDCR, M2OIDEQUIPEMENT) TABLESPACE INDX;
CREATE INDEX m2oequipement_i1 ON M2OEQUIPEMENT(M2OADRESSERADIO, M2OTECHNO, SYSCLASS) TABLESPACE INDX;
CREATE INDEX m2ohrnbrecepcr_i1 ON M2OHRNBRECEPCR(M2OIDEQUIPEMENT, M2OIDCR, M2OTRAITE) TABLESPACE INDX;
CREATE INDEX CI4B2EE1C72FE0CBDA ON M2OCOMPTEUR(M2OPOINTCOMPTAGE_ID)

Que faut-il que je fasse ? il doit bien avoir un autre moyen d'écrire cette requête update...
Ou est-ce qu'il faut que passe en pl/sql avec un curseur ?

please help !
merci d'avance,
Renaud

[al1_24]

Un autre moyen que de passer par un curseur est de passer par une table temporaire.

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
CREATE GLOBAL TEMPORARY TABLE tt_travail
AS  SELECT  id
        ,   valeur1
        ,   valeur2
    FROM    marequete_set
;
UPDATE  matable tbl
SET (   valeur1
    ,   valeur2
    =   (   SELECT  tmp.valeur1
                ,   tmp.valeur2
            FROM    tt_travail  tmp
            WHERE   tmp.id = tbl.id
        )
WHERE   EXISTS
        (   SELECT  1
            FROM    tt_travail  sel
            WHERE   sel.id = tbl.id
        )
;

[rcb73]

merci pour l'idée, en effet un collègue m'a déjà proposé de faire avec la création d'une table (je ne connaissais pas la notion de table temporaire, je v regarder ce que c'est).
peux-tu m'expliquer en quoi cela peux me faire gagner du temps ? est-ce que dans mon premier exemple oracle ré-exécute la requête qui est dans le "set" à chaque occurrence présente dans le "where exists" ?

N'existe-t-il pas un moyen de faire une jointure entre ma requête présente dans le "set" et celle dans le "where exists" ?

j'ai également une question sur les index, je remarque que lorsque la condition sur la colonne est 'is null' or 'is not null', Oracle n'utilise pas l'index présent sur la colonne, je ne comprends pas pourquoi ?

merci beaucoup pour votre aide.

[skuatamad]

Utilise MERGE pour éviter la partie EXISTS de la requête

[rcb73]

merci skuatamad, effectivement je ne connaissais pas l'instruction MERGE, ça me paraît assez intéressant pour notre cas.

nous allons étudier ça.

merci pour votre aide.

[PpPool]

Utilise MERGE pour éviter la partie EXISTS de la requête

+1 - sur la version 11g, nous avons grandement optimiser des UPDATE avec une sous-requête avec un MERGE WHEN MATCHED - belle surprise de la 11g !

[rcb73]

grand merci skuatamad, nous avons divisé par 100 le temps d'exécution en passant par le MERGE

merci à tous pour votre aide.

[Mohamed.Houri]

grand merci skuatamad, nous avons divisé par 100 le temps d'exécution en passant par le MERGE

merci à tous pour votre aide.

Pour que votre question nous serve à nous tous, je vous saurai gré de bien vouloir faire ceci

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
 
explain plan for
UPDATE m2oPointComptage pds
SET m2oDateRaccorde =
  ( SELECT DISTINCT first_value(e.m2oDateDerRx) over (partition BY pds.sysId ORDER BY e.sysUpdTime)
  FROM m2oCompteur cpt,
    m2oEquipement eq,
    m2oHrNbRecepCr e
  WHERE e.m2oTraite        = 'N'
  AND e.m2oIdEquipement    = eq.m2oAdresseRadio
  AND e.m2oTypeDeTrame     = '3'
  AND eq.sysClass          = 'm2oModule'
  AND eq.m2oTechno         = 'HR'
  AND cpt.sysId            = eq.m2ocompteur_id
  AND pds.sysId            = cpt.m2opointcomptage_id
  AND e.m2oDateDerRx      IS NOT NULL
  AND pds.m2oDateRaccorde IS NULL
  )
WHERE pds.m2oDateRaccorde IS NULL
AND EXISTS
  (SELECT 1
  FROM m2oCompteur cpt,
    m2oEquipement eq,
    m2oHrNbRecepCr e
  WHERE e.m2oTraite     = 'N'
  AND e.m2oIdEquipement = eq.m2oAdresseRadio
  AND e.m2oTypeDeTrame  = '3'
  AND eq.sysClass       = 'm2oModule'
  AND eq.m2oTechno      = 'HR'
  AND cpt.sysId         = eq.m2ocompteur_id
  AND pds.sysId         = cpt.m2opointcomptage_id
  AND e.m2oDateDerRx   IS NOT NULL
  );
 
select * from table(dbms_xplan.display);

et aussi ceci (afin de pouvoir faire une comparaison)

Code :

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

1
2
3
4
5
6
7
8
 
explain plan for
MERGE into
       m2oPointComptage pds
....
;
 
select * from table(dbms_xplan.display);

et postez ici les deux plans d'exécution pour que l'on puisse comparer la différence.

[X452-625]

Inspiré de merge, j'essaie d'optimiser mes requêtes update .
Voici la requete :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
13
14
merge into m2oEquipement eq 
using ( select distinct eq.sysId eqSysId, first_value(e.m2oDateDerRx) over (partition by eq.m2oAdresseRadio order by e.sysUpdTime) m2oDateDerRx
from m2oEquipement eq, m2oHrNbRecepCr e 
where e.m2oTraite = 'N' 
and e.m2oIdEquipement = eq.m2oAdresseRadio 
and e.m2oTypeDeTrame = '3' 
and e.m2oDateDerRx is not null 
and eq.m2oDateRaccorde IS NULL 
and eq.sysClass = 'm2oModule' 
and eq.m2oTechno = 'HR' 
and eq.m2oReleve != 'WB' ) tmp
on ( eq.sysId = tmp.eqSysId )
when matched then 
update set eq.m2oDateRaccorde = tmp.m2oDateDerRx ;

Mais dans SQL Developper, j'ai toujours cette erreur :

Code :

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

Erreur SQL : Il n'y a plus de données à lire dans le socket

J'ai cherché un peu sur internet mais je ne trouve pas d'information sur ce type d'erreur.
Est-ce qu'il y a des conditions pour utiliser merge?

Merci d'avance
Nam

[mnitu]

Affichez toujours le numéro d’erreur Oracle. Quelle version d’Oracle utilisez vous ?

[X452-625]

C'est ça le problème. C'est tout ce que j'ai comme message d'erreur quand je test cette requête dans SQL Developper.
Je n'ai pas de numéro d’erreur Oracle.
Ma version d'Oracle est : 11.2.0.1.0.

[mnitu]

Exécutez votre requête en sqlplus.