Vue récalcitrante 7.3.4

Version imprimable

Voir 40 message(s) de cette discussion en une page

Autant pour moi, j'ai vu après coup qu'il manquait les parenthèses ! j'essaye :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SELECT me.*
  FROM mvt_ecritures me
 WHERE (   EXISTS (
              SELECT 1
                FROM groupes grp
               WHERE me.service_fk = grp.service_fk
                     AND grp.operateur_fk = USER)
        OR EXISTS (
              SELECT 1
                FROM services ser
               WHERE me.service_fk = ser.service_id
                   AND EXISTS (
                       SELECT 1 from utilisateurs utl
                          WHERE (ser.division_fk = utl.division_fk or utl.division_fk is null)
				AND (ser.service_id = utl.service_fk or utl.service_fk is null)
                                 AND utl.operateur_id||'' = USER)
                              )
       )
   AND SUBSTR (me.compte_fk, 1, 1) NOT IN ('1', '5')
   AND SUBSTR (me.compte_fk, 1, 2) NOT IN ('64', '42', '43', '45', '47', '49')

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
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
SELECT STATEMENT                                                                                            RULE
  SORT                                             GROUP BY                                     1         1
    FILTER                                                                                      1    1    2
      MERGE JOIN                                   OUTER                                        1    2    3
        NESTED LOOPS                               OUTER                                        1    3    4
          FILTER                                                                                1    4    5
            NESTED LOOPS                           OUTER                                        1    5    6
              TABLE ACCESS                         BY ROWID             MVT_ECRITURES           1    6    7
                INDEX                              RANGE SCAN           I_MVT_ECR_ECR_MOUVEM    1    7    8
              TABLE ACCESS                         BY ROWID             COMPTES                 2    6    9
                INDEX                              UNIQUE SCAN          COMPTE_PK               1    9   10
          TABLE ACCESS                             BY ROWID             TIERS                   2    4   11
            INDEX                                  UNIQUE SCAN          TIERS_PK                1   11   12
        SORT                                       JOIN                                         2    3   13
          VIEW                                                          V_MVT_ECRITURE_COMME    1   13   14
            FILTER                                                                              1   14   15
              TABLE ACCESS                         FULL                 MVT_ECRITURES           1   15   16
              INDEX                                UNIQUE SCAN          GROUPE_PK               2   15   17
              NESTED LOOPS                                                                      3   15   18
                TABLE ACCESS                       BY ROWID             SERVICES                1   18   19
                  INDEX                            UNIQUE SCAN          SERVICE_PK              1   19   20
 
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
                TABLE ACCESS                       FULL                 UTILISATEURS            2   18   21
      INDEX                                        UNIQUE SCAN          GROUPE_PK               2    2   22
      NESTED LOOPS                                                                              3    2   23
        TABLE ACCESS                               BY ROWID             SERVICES                1   23   24
          INDEX                                    UNIQUE SCAN          SERVICE_PK              1   24   25
        TABLE ACCESS                               FULL                 UTILISATEURS            2   23   26
 
27 ligne(s) sélectionnée(s).

Non seulement il fait un FULL sur la table MVT_ECRITURES, mais aussi un FULL sur UTILISATEURS !

Citation:

Il faudrait partir de la requête qui contient la table directement qui a le bon plan d'exécution d'après ce que tu dis, puis remplacer par une vue qui fait un simple "select *", puis une vue qui fait une simple jointure avec la table des groupes etc... etc... Et, au moment ou le FULL apparait se concentrer sur le problème.

OK
La requête en attaquant la table MVT_ECRITURES directement :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
SELECT STATEMENT                                                                                            RULE
  SORT                                             GROUP BY                                     1         1
    NESTED LOOPS                                   OUTER                                        1    1    2
      NESTED LOOPS                                 OUTER                                        1    2    3
        FILTER                                                                                  1    3    4
          NESTED LOOPS                             OUTER                                        1    4    5
            TABLE ACCESS                           BY ROWID             MVT_ECRITURES           1    5    6
              INDEX                                RANGE SCAN           I_MVT_ECR_ECR_MOUVEM    1    6    7
            TABLE ACCESS                           BY ROWID             COMPTES                 2    5    8
              INDEX                                UNIQUE SCAN          COMPTE_PK               1    8    9
        TABLE ACCESS                               BY ROWID             TIERS                   2    3   10
          INDEX                                    UNIQUE SCAN          TIERS_PK                1   10   11
      TABLE ACCESS                                 BY ROWID             MVT_ECRITURES           2    2   12
        INDEX                                      RANGE SCAN           I_MVT_ECR_ECR_ORIGIN    1   12   13
 
14 ligne(s) sélectionnée(s).

Vraiment, si je peux obtenir un tel plan d'exécution, je paye le coup !
Test avec une vue simple :

Code:

1
2
SELECT me.*
  FROM mvt_ecritures me

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
SELECT STATEMENT                                                                                            RULE
  SORT                                             GROUP BY                                     1         1
    NESTED LOOPS                                   OUTER                                        1    1    2
      NESTED LOOPS                                 OUTER                                        1    2    3
        FILTER                                                                                  1    3    4
          NESTED LOOPS                             OUTER                                        1    4    5
            TABLE ACCESS                           BY ROWID             MVT_ECRITURES           1    5    6
              INDEX                                RANGE SCAN           I_MVT_ECR_ECR_MOUVEM    1    6    7
            TABLE ACCESS                           BY ROWID             COMPTES                 2    5    8
              INDEX                                UNIQUE SCAN          COMPTE_PK               1    8    9
        TABLE ACCESS                               BY ROWID             TIERS                   2    3   10
          INDEX                                    UNIQUE SCAN          TIERS_PK                1   10   11
      TABLE ACCESS                                 BY ROWID             MVT_ECRITURES           2    2   12
        INDEX                                      RANGE SCAN           I_MVT_ECR_ECR_ORIGIN    1   12   13
 
14 ligne(s) sélectionnée(s).

Pas mal non plus !
Avec une simple jointure avec la table GROUPES telle que proposée par remi4444 :

Code:

1
2
3
4
5
6
7
8
SELECT me.*
  FROM mvt_ecritures me
 WHERE (   EXISTS (
              SELECT 1
                FROM groupes grp
               WHERE me.service_fk||'' = grp.service_fk
                     AND grp.operateur_fk||'' = USER)
   )

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
SELECT STATEMENT                                                                                            RULE
  SORT                                             GROUP BY                                     1         1
    FILTER                                                                                      1    1    2
      MERGE JOIN                                   OUTER                                        1    2    3
        NESTED LOOPS                               OUTER                                        1    3    4
          FILTER                                                                                1    4    5
            NESTED LOOPS                           OUTER                                        1    5    6
              TABLE ACCESS                         BY ROWID             MVT_ECRITURES           1    6    7
                INDEX                              RANGE SCAN           I_MVT_ECR_ECR_MOUVEM    1    7    8
              TABLE ACCESS                         BY ROWID             COMPTES                 2    6    9
                INDEX                              UNIQUE SCAN          COMPTE_PK               1    9   10
          TABLE ACCESS                             BY ROWID             TIERS                   2    4   11
            INDEX                                  UNIQUE SCAN          TIERS_PK                1   11   12
        SORT                                       JOIN                                         2    3   13
          VIEW                                                          V_MVT_ECRITURE_COMME    1   13   14
            FILTER                                                                              1   14   15
              TABLE ACCESS                         FULL                 MVT_ECRITURES           1   15   16
              TABLE ACCESS                         FULL                 GROUPES                 2   15   17
      TABLE ACCESS                                 FULL                 GROUPES                 2    2   18
 
19 ligne(s) sélectionnée(s).

Cela ressemble au test que j'ai fait précédemment FULL sur MVT_ECRITURES + GROUPES.
Je le redonne :

Code:

1
2
3
4
5
6
7
8
SELECT me.*
  FROM mvt_ecritures me
 WHERE (   EXISTS (
              SELECT 1
                FROM groupes grp
               WHERE me.service_fk = grp.service_fk
                     AND grp.operateur_fk = USER)
       )

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
SELECT STATEMENT                                                                                            RULE
  SORT                                             GROUP BY                                     1         1
    FILTER                                                                                      1    1    2
      MERGE JOIN                                   OUTER                                        1    2    3
        NESTED LOOPS                               OUTER                                        1    3    4
          FILTER                                                                                1    4    5
            NESTED LOOPS                           OUTER                                        1    5    6
              TABLE ACCESS                         BY ROWID             MVT_ECRITURES           1    6    7
                INDEX                              RANGE SCAN           I_MVT_ECR_ECR_MOUVEM    1    7    8
              TABLE ACCESS                         BY ROWID             COMPTES                 2    6    9
                INDEX                              UNIQUE SCAN          COMPTE_PK               1    9   10
          TABLE ACCESS                             BY ROWID             TIERS                   2    4   11
            INDEX                                  UNIQUE SCAN          TIERS_PK                1   11   12
        SORT                                       JOIN                                         2    3   13
          VIEW                                                          V_MVT_ECRITURE_COMME    1   13   14
            FILTER                                                                              1   14   15
              TABLE ACCESS                         FULL                 MVT_ECRITURES           1   15   16
              INDEX                                UNIQUE SCAN          GROUPE_PK               2   15   17
      INDEX                                        UNIQUE SCAN          GROUPE_PK               2    2   18
 
19 ligne(s) sélectionnée(s).

On y est là, le FULL commence dès le premier EXISTS avec la sous-requête sur la table GROUPES.

Citation:

En écrivant ça, il apparait aussi une autre solution dont j'avais déja parlé qui serait de tenir à jour une table de correspondance contenant simplement 2 colonnes: service_id et user_id avec la clef primaire dans cet ordre, cette table pourra même être organisée en index.

Code:

1
2
3
4
5
6
7
SELECT me.*
  FROM mvt_ecritures me
 WHERE EXISTS
       ( select 1 from  IOT_SRV_USER usr where me.service_fk = usr.service_id
            and usr.user_id = USER)
   AND SUBSTR (me.compte_fk, 1, 1) NOT IN ('1', '5')
   AND SUBSTR (me.compte_fk, 1, 2) NOT IN ('64', '42', '43', '45', '47', '49')

Ce n'est peut être pas la peine de tester vu qu'il est à quelque chose près identique à la vue précédente !

Citation:

Personnellement j'essayerais d'insister sur une construction d'une table qui regroupe toute les correspondance.

J'essayerai dans un 4ème temps.

Citation:

J'ai l'impression qu'il y a une erreur dans ta définition car il y à 2 fois la meme chose des 2 cotés du OR....

Comme je le disais, j'ai changé ma vue pour simuler une table de correspondances en gardant l'accès à GROUPES et en le réutilisant pour la table de correspondances.

Citation:

est-ce que la colonne grp.service_fk est bien indexée ??

Non, je crois de toute façon que cela n'a pas d'importance en soit car la table GROUPES est ridiculement petite !
Donc, je crois pouvoir me permettre l'accès à cette table en RANG ou UNIQUE SCAN.

Citation:

Donc peut etre que la bonne methode est effectivement de faire cette séparation en tete de vue par des "union all" s'excluants les uns et les autres.
...
Autre méthode que j'ai parfois utilisé avec succés, c'est de s'appuyer carrément sur une fonction PL/SQL. Il faut compliler cette fonction avec la directive DETERMINISTIC pour indiquer à oracle de ne pas la réévaluer en cours de requête quand les paramètres ne changent pas.

La méthode avec UNION ALL va être plus longue à tester, aussi j'essayerai dans un 5ème temps.
Par contre la méthode avec un fonction PL/SQL me parait, d'un part, plus intéressante à étudier, et d'autre part, rapide à mettre en oeuvre.
Pour la directive DETERMINISTIC, j'ai bien peur qu'elle n'existe pas sous la 7.3.4. Je vais faire sans.

Alors, j'ai créé une fonction stockée tel que tu me proposes :

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
36
37
38
39
40
41
42
43
44
45
46
FUNCTION acces_service(code_svce IN VARCHAR2 := NULL) RETURN NUMBER IS
indic_existe NUMBER(1) := 0;
BEGIN
 
 
select
1 into indic_existe
from
(
(
select
service_fk svce
from
groupes grp
where
grp.operateur_fk = user
)
union
(
select
service_id svce
from
services ser,
utilisateurs utl
where
ser.division_fk like utl.division_fk||'%' and
ser.service_id like utl.service_fk||'%' and
utl.operateur_id = user
)
)
where
svce = code_svce;
 
 
RETURN(indic_existe);
 
 
EXCEPTION
WHEN no_data_found THEN
	RETURN(0);
 
WHEN others THEN
	RETURN(-1);
 
 
END acces_service;

Puis modifier ma vue sans les NOT IN d'abord :

Code:

1
2
3
4
5
select
me.*
from mvt_ecritures me
where
acces_service(me.service_fk) = 1

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
SELECT STATEMENT                                                                                            RULE
  SORT                                             GROUP BY                                     1         1
    NESTED LOOPS                                   OUTER                                        1    1    2
      NESTED LOOPS                                 OUTER                                        1    2    3
        FILTER                                                                                  1    3    4
          NESTED LOOPS                             OUTER                                        1    4    5
            TABLE ACCESS                           BY ROWID             MVT_ECRITURES           1    5    6
              INDEX                                RANGE SCAN           I_MVT_ECR_ECR_MOUVEM    1    6    7
            TABLE ACCESS                           BY ROWID             COMPTES                 2    5    8
              INDEX                                UNIQUE SCAN          COMPTE_PK               1    8    9
        TABLE ACCESS                               BY ROWID             TIERS                   2    3   10
          INDEX                                    UNIQUE SCAN          TIERS_PK                1   10   11
      TABLE ACCESS                                 BY ROWID             MVT_ECRITURES           2    2   12
        INDEX                                      RANGE SCAN           I_MVT_ECR_ECR_ORIGIN    1   12   13
 
14 ligne(s) sélectionnée(s).

Alors là bravo !
Pile poil le même plan d'exécution qu'en accédant à la table directement.
Chapeau bas ! ça me semble impeccable maintenant, je teste tout de suite pour apprécier les temps de réponse.
C'est quasi instantanné, super !

J'essaye avec les NOT IN :

Code:

1
2
3
4
5
6
7
select
me.*
from mvt_ecritures me
where
acces_service(me.service_fk) = 1 and
substr(me.compte_fk, 1, 1) not in ('1', '5') and
substr(me.compte_fk, 1, 2) not in ('64', '42', '43', '45', '47', '49')

L'explain plan est identique, donc les NOT IN ne gènent pas.

Pas mal !
Merci remi4444. Grâce à ton aide et à ta perspicacité, j'ai LA SOLUTION, cool !

Mais bon, avant de crier victoire, je me demande quand même si la directive DETERMINISTIC n'est pas un prérequis à cette méthode.
Par ailleurs, on ne voit plus les accès aux tables GROUPES, SERVICES et UTILISATEURS.
J'ai l'impression que l'optimiseur est complètement à côté de la plaque et qu'il n'y voit que du feu !

Pensez-vous que je tiens là la solution à ma problématique ?
Est-elle orthodoxe et conforme à l'état de l'art en la matière, ou bien plutôt tirée par les cheveux ?
Est-il envisageable de l'appliquer en production pensez-vous ?
.

Citation:

Envoyé par star

Pour la directive DETERMINISTIC, j'ai bien peur qu'elle n'existe pas sous la 7.3.4. Je vais faire sans.

:oops: j'avais fini par oublié ce petit détail de la taille d'un mamouth :aie: ...

Citation:

Envoyé par star

Citation:

est-ce que la colonne grp.service_fk est bien indexée ??

Non, je crois de toute façon que cela n'a pas d'importance en soit car la table GROUPES est ridiculement petite !

Là tu fais une erreur dans ton raisonnement à mon avis, parceque tu oublies que tu est en RBO et donc que pour oracle les tables groupes ou mvt_ecritures ont le meme poids, oracle ne sais pas si GROUPES contient 1 milliard de lignes ou 2 lignes. Si sur ta jointure, tu as une colonne indexée et l'autre pas, oracle va obligatoirement choisir un chemin, meme si l'autre aurait été 100 fois moins couteux, et c'est l'ensemble de ton plan d'exéctution qui va être chamboulé. Si j'ai voulu testé la vue en disqualifiant des index, c'est précisément pour detecter les points qui coincent, et la non-présence d'index sur une table meme toute petite peut être lourde de conséquences...

Pour qu'au moins un chemin entierement indexé soit possible, il faut donc tester en créant des indexs sur: (peut etre sur des copies de tables puisque tu es en prod)

- grp.service_fk
- ser.service_id
- utl.division_fk
- utl.service_fk

Puis refaire la vue avec les "( ... OR ... is null)" on sait jamais si ça pouvait fonctionner comme ça, ça serais dommage de passer à coté...

Ensuite je ne voudrais pas te créer une fausse joie avec la solution par la fonction, c'est normal que le plan soit "bon" parceque toutes les tables problématiques sont parties. Mais il faut avoir à l'esprit que la fonction sera appelée autant de fois que de lignes accédées dans mvt_ecritures. D'ou l'interret du DETERMINISTIC (qui n'existe pas! :aie: )

parenthèse:

Pour tester le nombre de fois qu'une fonction est appelée à l'intérrieur d'une requête, moi je crée une séquence de débugage, je commence le traitement de ma fonction en faisant un "select SEQ_DEBUG.nextval into toto from dual", puis je fait une simple différence entre l'état de la séquence avant et aprés l'exécution de la requête.

fin de parenthèse:

Il faut donc absoluement faire la vue de manière plus fine en faisant une jointure avec la table service, ainsi tu va fortement baisser le nombre d'appel à la fonction (enfin ça se teste justement...).
Code:

1 2 3 4 5 6 7 8 9 10 11 SELECT me.* FROM mvt_ecritures me WHERE EXISTS ( EXISTS ( SELECT 1 FROM services ser WHERE me.service_fk = ser.service_id AND acces_service(ser.service_id) = 1 ) AND SUBSTR (me.compte_fk, 1, 1) NOT IN ('1', '5') AND SUBSTR (me.compte_fk, 1, 2) NOT IN ('64', '42', '43', '45', '47', '49')
Apres il faut faire tres attention à soigner ta fonction pour que chaque requête soit opimisée au maximum, l'interret de passer par là est justement de ne plus faire les astuces de traitement général avec les LIKE et les UNION.

En gros je verrais l'algorythme suivant:

- Test de la présence dans la table GROUPE

- sinon , test des 2 valeurs de (utl.service_fk, utl.division_fk) par un select dans la table UTILISATEUR, tu va avoir 4 cas:
* (null, null) : Alors retour 1 (le user à tous les droits)
* (not null, null) :
Test d'égalité entre le service_fk du user et celui passé en paramètre
* (not null, not null) test précédent + recupération de la division_fk de la table service pour le service_id passé en paramètre + test de cette valeur avec celle du user;
* (null, not null) :
Pas de test sur le service du user, recupération de la division_fk de la table service pour le service_id passé en paramètre + test de cette valeur avec celle du user;

Ainsi tu ne limites tes requêtes internes à ta fonction qu'a des choses simplissimes.

Je te conseillerais trés fortement quand meme de faire un test meme en environnement de prod!! tu peux trés bien creer une vue temporaire MVT_ECRITURES_TEST et faire tes requêtes avec...

Voilà... quant à savoir si c'est vraiment dans les règles de l'art... bof... déja y'a un truc qui l'est pas, c'est de continuer à travailler en version 7.3.4 en 2006 :aie: ;)

11/10/2006, 20h21
star

Citation:

il faut avoir à l'esprit que la fonction sera appelée autant de fois que de lignes accédées dans mvt_ecritures. D'ou l'interret du DETERMINISTIC (qui n'existe pas!

quant à savoir si c'est vraiment dans les règles de l'art... bof... déja y'a un truc qui l'est pas, c'est de continuer à travailler en version 7.3.4 en 2006

Ma foi, J'ai encore 1 mois à tenir en 7.3.4 avant de passer sous la 10gR2 sur un IBM p5 550 2 Dual Core Modules avec 16Go de RAM. La directive DETERMINISTIC pourra à ce moment là être mise en oeuvre.

Citation:

En gros je verrais l'algorythme suivant:

- Test de la présence dans la table GROUPE

- sinon , test des 2 valeurs de (utl.service_fk, utl.division_fk) par un select dans la table UTILISATEUR, tu va avoir 4 cas:
* (null, null) : Alors retour 1 (le user à tous les droits)
* (not null, null) :
Test d'égalité entre le service_fk du user et celui passé en paramètre
* (not null, not null) test précédent + recupération de la division_fk de la table service pour le service_id passé en paramètre + test de cette valeur avec celle du user;
* (null, not null) :
Pas de test sur le service du user, recupération de la division_fk de la table service pour le service_id passé en paramètre + test de cette valeur avec celle du user;

Ainsi tu ne limites tes requêtes internes à ta fonction qu'a des choses simplissimes.

Merci pour ces quelques conseils forts pertinents. Je vais travailler dessus dès que possible.

En attendant, je vais opter pour la fonction PL/SQL et plannifier sous la 10g l'utilisation de la directive DETERMINISTIC en même temps qu'une révision en profondeur de la fonction pour de meilleurs temps de réponse.

Le sujet mérite tout de même une petite étoile (star) à mon sens, non !
.

Voir 40 message(s) de cette discussion en une page