Bonjour à tous,

Je vais essayer d'être clair, c'est pas forcément gagné !

Oracle9i Release 9.2.0.1.0 - Production
PL/SQL Release 9.2.0.1.0 - Production

J'ai un traitement PL qui me permet de créer une table dynamique en fonction d'une chaine SQL passée en paramètre.
La chaine SQL est assez complexe, elle fait notamment de nombreux appels à DBMS_RANDOM.value, ce pourquoi je suis obligé de créer une table physique pour l'exploiter ensuite.

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) ;
J'obtiens une table de ce type :

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
9
10
11
 
SQL> DESC TL_TEST
 Nom                                       NULL ?   Type
 ----------------------------------------- -------- ---------------
 POINT_ID                                           NUMBER(38)
 METH_ID                                            VARCHAR2(50)
 FLACO_ID                                           CHAR(1)
 ETIQ_REPLIQUE                                      NUMBER
 RESU_REPONSE                                       NUMBER
 RESU_QUANTITATIF                                   NUMBER
 RESU_MESURE                                        NUMBER
la table comporte 4000 lignes. Je crée dynamiquement un index sur la colonne FLACO_ID dans mon traitement également.
Nota : la création de la table est bien optimisée, c'est quasiment instantanée.

Je passe ensuite la requete suivante (que j'ai simplifié pour décrire le problème)

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


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
 
Plan d'exécution
----------------------------------------------------------
   0      SELECT STATEMENT Optimizer=CHOOSE
   1    0   MERGE JOIN
   2    1     MERGE JOIN
   3    2       VIEW
   4    3         SORT (GROUP BY)
   5    4           TABLE ACCESS (BY INDEX ROWID) OF 'TL_TEST'
   6    5             INDEX (RANGE SCAN) OF 'I_4C48EPJAMV301GR9KQXR' (
          NON-UNIQUE)
 
   7    2       SORT (JOIN)
   8    7         VIEW
   9    8           FILTER
  10    9             SORT (GROUP BY)
  11   10               TABLE ACCESS (FULL) OF 'TL_TEST'
  12    1     SORT (JOIN)
  13   12       VIEW
  14   13         SORT (GROUP BY)
  15   14           TABLE ACCESS (BY INDEX ROWID) OF 'TL_TEST'
  16   15             INDEX (RANGE SCAN) OF 'I_4C48EPJAMV301GR9KQXR' (
          NON-UNIQUE)
Le pb que je cherche à résoudre est ici l'ACCESS FULL pour les calculs d'agrégats.

- J'ai essayé de créer une colonne PK supplémentaire à ma table alimentée par une séquence, mais cela n' a pas résolu mon problème (cela ralentit même quelque peu ma création de table dynamique)
- J'ai essayé d'ajouter un HINT /*+ ROWID(TL_TEST) */ mais celui-ci n'est pas pris en compte.

Aujourd'hui, la requete que je vous présente est une requete sous-imbriquée d'une autre requete que j'ai tracé. La requete finale réalise un calcul d'agrégat globlal (variance, moyenne)+ différents calculs.

je l'ai lancé 6 fois, et les temps de réponse sont médiocres.

Code : Sélectionner tout - Visualiser dans une fenêtre à part
1
2
3
4
5
6
7
8
 
call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        6      0.01       0.00          0          0          0           0
Execute      6      0.00       0.00          0          0          0           0
Fetch        6      2.85       2.84         15      27822          0           6
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total       18      2.87       2.86         15      27822          0           6
J'ai réellement besoin d'optimiser cette requete. Elle est incluse dans un traitement itératif qui peut être lancé 50 à 100 fois.

Je tiens à préciser que j'ai bien conscience que les fonctions analytiques pourraient m'être d'un grand secours dans mon cas.
J'ai passé 1 mois là dessus, et je suis resté finalement sur la requete que je vous propose. Les fonctions analytiques ont été plus fortes que moi

Voilà, merci d'avance pour votre aide (sorry pour le long post, j'ai essayé d'être précis)
@