Discussion :

[PpPool]

j'ai pas regardé en profondeur mais il semble que personne n'a conseillé les fonctions analytiques alors que ça semble pouvoir régler quelque soucis

salut Fred,

comme je l'ai précisé, j'ai passé 1 mois sur le sujet, et j'ai jamais réussi à m'en sortir comme je voulai avec les fonctions analytiques

Aujourd'hui la requete présentée fonctionne très bien dans la plupart des cas, sauf quand je suis dans le cas d'une création de table dynamique, comme décrit ci-dessus.

@

[Jaouad]

Tu devrais essayer avec un index sur Flaco_ID

j'ai bien un index sur FLACO_ID

je voulais dire POINT_ID

et effectivement il faudrait qu'il regarde les fonctions analytiques , lui même le sait , et d'ailleurs nous lui avons dit de ré écrire sa requête

[PpPool]

Description des statistiques de la table TL_TEST :

* Blocs vides : 1
* Espace moyen : 327
* Nombre de lignes : 4000
* Taille de l'échantillon : 4000
* Longueur moyenne des lignes : 43
* Nombre de lignes continues : 2276
* Nombre moyen des blocs de listes d'espaces libres : 3372
* Nombre de blocs de listes d'espaces libres : 4

et cela obtenu après :

Code :

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

1
2
 
analyze table TL_TEST compute statistics  for table for all indexed columns ;

[orafrance]

peut se calculer avec :

Code :

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

1
2
3
SELECT SUM( CASE WHEN FLACO_ID = 'A' THEN 1 ELSE 0 END),
       SUM( CASE WHEN FLACO_ID = 'B' THEN 1 ELSE 0 END)
FROM ...

Bonjour Médiat et merci de ton aide

oui, mais avec le cas que tu cites comment veux tu par exemple faire un calcul de variance totale pour toutes les mesures obtenus sur le flacon A ?

@

et ça ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT   point_id, AVG (resu_mesure) AS moy_a,flaco_id
                 (CASE
                     WHEN (    COUNT (resu_mesure) >= 2
                           AND SUM (NVL (resu_quantitatif, 0)) = 0
                          )
                        THEN VARIANCE (resu_mesure)
                     ELSE NULL
                  END
                 ) AS var_a
            FROM tl_test
           WHERE flaco_id IN ('A','B')
        GROUP BY point_id,flaco_id

[orafrance]

ce serait bien d'avoir le code complet pour faire des tests

[ducho]

bonsoir,

le nb de blocs parcourus semble s' expliquer par le nombre de
lignes continues .
il serait peut-être interessant de préciser les paramétres de création de
la table ( qui semblent être ceux paz défaut du tablespace ) .

qu' en pensez-vous ?

[PpPool]

peut se calculer avec :

Code :

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

1
2
3
SELECT SUM( CASE WHEN FLACO_ID = 'A' THEN 1 ELSE 0 END),
       SUM( CASE WHEN FLACO_ID = 'B' THEN 1 ELSE 0 END)
FROM ...

Bonjour Médiat et merci de ton aide

oui, mais avec le cas que tu cites comment veux tu par exemple faire un calcul de variance totale pour toutes les mesures obtenus sur le flacon A ?

@

et ça ?

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
SELECT   point_id, AVG (resu_mesure) AS moy_a,flaco_id
                 (CASE
                     WHEN (    COUNT (resu_mesure) >= 2
                           AND SUM (NVL (resu_quantitatif, 0)) = 0
                          )
                        THEN VARIANCE (resu_mesure)
                     ELSE NULL
                  END
                 ) AS var_a
            FROM tl_test
           WHERE flaco_id IN ('A','B')
        GROUP BY point_id,flaco_id

Tout d'abord, merci encore à tous de vous intéresser à mon pb.

c'est une requete métier très complexe, que j'ai bcq simplifié pour poser le problème sur le forum.

Je vais me repencher sur les fonctions analytiques, peut-etre cette fois -ci avec plus de succés !

[orafrance]

bonsoir,

le nb de blocs parcourus semble s' expliquer par le nombre de
lignes continues .
il serait peut-être interessant de préciser les paramétres de création de
la table ( qui semblent être ceux paz défaut du tablespace ) .

qu' en pensez-vous ?

Moi j'suis ni gourou ni voyant extralucide

Sans trace, impossible de connaitre les événements d'attente et donc les axes d'amélioration

En effet, les fonctions analytiques risque de ne pas trop t'aider... essaye de voir comment ne faire qu'une seule requête et de filtrer le résultat de cette requête selon ton besoin, j'ai l'impression que tu cherches à faire quelques chose de très complexe en une passe qui pourrait être bien plus simple en plusieurs étape

[PpPool]

bonsoir,

le nb de blocs parcourus semble s' expliquer par le nombre de
lignes continues .
il serait peut-être interessant de préciser les paramétres de création de
la table ( qui semblent être ceux paz défaut du tablespace ) .

qu' en pensez-vous ?

bonjour ducho,

voici les scripts de création que j'ai utilisé :

a/ pour les tablespaces

Code :

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

1
2
3
4
5
 
--STATISTIQUES (NOLOGGING)
CREATE TABLESPACE TP_USR_STATS1
DATAFILE 'D:\oracle\oradata\local\TP_USR_STATS1_01.DBF' size 20M
EXTENT MANAGEMENT LOCAL AUTOALLOCATE NOLOGGING;

b/ pour la table dynamique

Code :

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

1
2
 
EXECUTE IMMEDIATE ('CREATE TABLE '||nom_table||' NOLOGGING CACHE TABLESPACE TP_USR_STATS1 AS '||chSQL) ;

[Médiat]

oui, mais avec le cas que tu cites comment veux tu par exemple faire un calcul de variance totale pour toutes les mesures obtenus sur le flacon A ?

J'arrive un peu après la bataille, mais avec le CASE WHEN tu transformes les valeur des flacons qui ne t'intéressent par des NULL, ils ne seront pas pris en compte dans le calcul de VARIANCE (je suppose, puisque non pris en compte dans le calcul de AVG) et tu auras bien la VARIANCE des flacon A...

[aline]

on peux prendre le problème autrement en le découpant.

Tu fais trois sous requêtes puis deux jointures.

Pourrais tu faire tourner chacune des trois séparement et nous poster les résultats (explain plan + tkprof)?

On pourrait vois ainsi si une sous requete est plus couteuse ou bien si c'est la jointure si c'est aucune des trois...

[Médiat]

A tout hasard, voila ce que j'avais en tête (je n'ai pas testé)

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
SELECT POINT_ID, AVG(RESU_MESURE) MOY_TOTAL, 
       AVG(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) MOY_A 
       AVG(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) MOY_B, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='A' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='A' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END ) VAR_A, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='B' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='B' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END) VAR_B 
FROM TL_TEST
GROUP BY POINT_ID
HAVING SUM(RESU_REPONSE)     < 4 
   AND SUM(RESU_QUANTITATIF) = 0

PS : j'ai retirer les NVL, inutiles, à ma connaissance, dans les calculs de SUM.

[PpPool]

A tout hasard, voila ce que j'avais en tête (je n'ai pas testé)

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
SELECT POINT_ID, AVG(RESU_MESURE) MOY_TOTAL, 
       AVG(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) MOY_A 
       AVG(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) MOY_B, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='A' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='A' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='A' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END ) VAR_A, 
      (CASE WHEN (COUNT(CASE WHEN FLACO_ID='B' THEN RESU_MESURE    ELSE NULL END) >= 2 
             AND  SUM(CASE WHEN FLACO_ID='B' THEN RESU_QUANTITATIF ELSE NULL END)  = 0) 
       THEN VARIANCE(CASE WHEN FLACO_ID='B' THEN RESU_MESURE ELSE NULL END) 
       ELSE NULL 
       END) VAR_B 
FROM TL_TEST
GROUP BY POINT_ID
HAVING SUM(RESU_REPONSE)     < 4 
   AND SUM(RESU_QUANTITATIF) = 0

PS : j'ai retirer les NVL, inutiles, à ma connaissance, dans les calculs de SUM.

Bonjour Médiat, et merci infiniment de ton aide

les résultats sont excellents. j'ai toujours un access full sur ma table malgré l'index sur le champ POINT_id

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
 
Ecoulé : 00 :00 :00.01
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE (Cost=48 Card=1 Bytes=43)
   1    0   FILTER
   2    1     SORT (GROUP BY) (Cost=48 Card=1 Bytes=43)
   3    2       TABLE ACCESS (FULL) OF 'TL_TEST' (Cost=13 Card=4000 By
          tes=172000)
 
 
Statistiques
----------------------------------------------------------
          0  recursive calls
          0  db block gets
         81  consistent gets
          0  physical reads
          0  redo size
     121840  bytes sent via SQL*Net to client
       1225  bytes received via SQL*Net from client
         68  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
       1000  rows processed

Je vais reprendre le problème posément. J'ai certainement voulu sortir la grosse usine à gaz en une seule requete.

mais grace à vous, j'ai désormais des pistes très intéressantes d'optimisation.

Merci encore une fois à tous VIVE DVP !!!!!

@

[Médiat]

j'ai toujours un access full sur ma table malgré l'index sur le champ POINT_id

C'est normal puisque le filtre se fait sur le HAVING du GROUP BY, il faut bien accéder à la totalité de la table au moins une fois pour constituer les groupes et faire les calculs...