[11g] Performance Update avec select au niveau du set

Version imprimable

2 pièce(s) jointe(s)

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:

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:

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

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

Code:

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
        )
;

17/06/2012, 20h07
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.
17/06/2012, 20h52
skuatamad

Utilise MERGE pour éviter la partie EXISTS de la requête
17/06/2012, 21h07
rcb73

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

nous allons étudier ça.

merci pour votre aide.
18/06/2012, 16h56
PpPool

Citation:

Envoyé par skuatamad

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 !
18/06/2012, 23h24
rcb73

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

merci à tous pour votre aide.;)

Citation:

Envoyé par rcb73

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

merci à tous pour votre aide.;)

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

Code:

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:

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.

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

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:

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

20/06/2012, 09h34
mnitu

Affichez toujours le numéro d’erreur Oracle. Quelle version d’Oracle utilisez vous ?
21/06/2012, 16h48
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.
21/06/2012, 17h05
mnitu

Exécutez votre requête en sqlplus.