Discussion :

[star]

Je souhaiterais obtenir votre aide sur la définition de la vue suivante qui me sert à filtrer les accès utilisateur aux lignes de la table sous-jacente.
Le problème qui se pose à moi-même est que j'obtiens des temps de réponse dramatiques lors des accès (SELECT en particulier).
Comment pourrais-je l'améliorer d'une certaine manière ?
Je vous en serais grandement reconnaissant !
PS Je ne suis pas au taff, donc pas d'explain plan sous la main, ne m'en veuillez pas !
.

OS AIX 4.3
Oracle 7.3.4

Vue :

select
me.*
from mvt_ecritures me
where
me.service_fk in
(
(
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
)
) 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')
with check option

[Xo]

Un petit effort sur l'indentation du code serait sympa

Sinon, as-tu été lire cet article : Optimisez votre SGBDR et vos requêtes SQL

En particulier, qq conseils à appliquer (sur les IN et les sous-requêtes) au niveau du $ 9. Transformations usuelles

[star]

Merci Xo pour la recommendation et le lien qui est, je pense, tjrs d'actualité.
J'ai essayé d'appliquer certaines transformations telles que celles suggérées par SQLPro, mais sans être arrivé à améliorer le plan d'exécution de mes requêtes au travers de ma vue.
Je m'en remet à vos lanternes, une petite étincelle m'aiderait !
Merci
.

[Xo]

As-tu pu modifier ta requête en supprimant des IN ou des sous-requêtes ? Si oui, peux-tu nous poster le résultat ?

[remi4444]

Essaye l'equivalent avec du vrai filtrage plutot que du "IN"

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
 
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, utilisateurs utl
               WHERE me.service_fk = utl.service_id
                 AND ser.division_fk LIKE utl.division_fk || '%'
                 AND ser.service_id LIKE utl.service_fk || '%'
                 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')

+ Les classiques de l'optimisations:
- Index sur les colonne de jointure des grosse tables (cadidats: me.service_fk , grp.service_fk, utl.service_id, ser.division_fk, ser.service_id, utl.service_fk )
- Passage des statistiques oracle.

[star]

Alors voilà la situation :

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
36
37
38
39
40
41
42
43
44
select
	TIES.RAISON_SOCIALE,
	M_EC_ORIGINES.TIERS_FK,
	M_EC_ORIGINES.EXERCICE_FK,
	M_EC_ORIGINES.SEQ_PIECE_ECRITURE_FK,
	M_EC_ORIGINES.CLASSE_JOURNAL_FK,
	M_EC_ORIGINES.DEF_PIECE_ECRITURE_FK,
	M_EC_ORIGINES.SEQ_MVT_ECRITURE_ID,
	nvl(M_EC_ORIGINES.MONTANT, 0) MONTANT,
	abs(sum(- decode(sign(to_date('03/11/2004') - M_EC_EMARGEANTS.DATE_VALIDATION), -1, 0, decode(M_EC_EMARGEANTS.INDIC_SENS, M_EC_ORIGINES.INDIC_SENS, 0, nvl(M_EC_EMARGEANTS.MONTANT, 0))) +
		decode(sign(to_date('03/11/2004') - M_EC_EMARGEANTS.DATE_VALIDATION), -1, 0, decode(M_EC_EMARGEANTS.INDIC_SENS, M_EC_ORIGINES.INDIC_SENS, nvl(M_EC_EMARGEANTS.MONTANT, 0), 0)))) EMARGE
from
	TIERS TIES,
	MVT_ECRITURES M_EC_EMARGEANTS,
	COMPTES CPTES,
	MVT_ECRITURES M_EC_ORIGINES
where
	TIES.TIERS_ID (+) = M_EC_ORIGINES.TIERS_FK and
	/*
	(M_EC_EMARGEANTS.INDIC_VALIDATION = 'C' or M_EC_EMARGEANTS.INDIC_VALIDATION is null) and
	*/
	M_EC_EMARGEANTS.SEQ_MVT_ORIGINE_FK (+) = M_EC_ORIGINES.SEQ_MVT_ECRITURE_ID and
	M_EC_EMARGEANTS.SEQ_PIECE_ORIGINE_FK (+) = M_EC_ORIGINES.SEQ_PIECE_ECRITURE_FK and
	M_EC_EMARGEANTS.EXERCICE_ORIGINE_FK (+) = M_EC_ORIGINES.EXERCICE_FK and
	M_EC_ORIGINES.SEQ_MVT_ORIGINE_FK is null and
	M_EC_ORIGINES.INDIC_SENS = 'D' and
	(CPTES.INDIC_NATURE = 'T' or CPTES.INDIC_NATURE is null) and
	CPTES.COMPTE_ID (+) = M_EC_ORIGINES.COMPTE_FK and
	M_EC_ORIGINES.INDIC_VALIDATION = 'C' and
	M_EC_ORIGINES.CLASSE_JOURNAL_FK = 'OR' and
	M_EC_ORIGINES.TYPE_JOURNAL_FK = 'GE' and
	M_EC_ORIGINES.SEQ_MVT_ECRITURE_ID = 39164 and
	M_EC_ORIGINES.SEQ_PIECE_ECRITURE_FK = 10772 and
	M_EC_ORIGINES.EXERCICE_FK = 2004
group by
	TIES.RAISON_SOCIALE,
	M_EC_ORIGINES.TIERS_FK,
	M_EC_ORIGINES.EXERCICE_FK,
	M_EC_ORIGINES.SEQ_PIECE_ECRITURE_FK,
	M_EC_ORIGINES.CLASSE_JOURNAL_FK,
	M_EC_ORIGINES.DEF_PIECE_ECRITURE_FK,
	M_EC_ORIGINES.SEQ_MVT_ECRITURE_ID,
	nvl(M_EC_ORIGINES.MONTANT, 0)
/

Avec cette requête, j'obtiens le plan d'exécution suivant au travers de ma vue :

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
36
37
38
39
40
41
42
43
44
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
SELECT STATEMENT                                                                                            RULE
  SORT                                             GROUP BY                                     1         1
    MERGE JOIN                                     OUTER                                        1    1    2
      SORT                                         JOIN                                         1    2    3
        NESTED LOOPS                               OUTER                                        1    3    4
          FILTER                                                                                1    4    5
            NESTED LOOPS                           OUTER                                        1    5    6
              NESTED LOOPS                                                                      1    6    7
                VIEW                                                                            1    7    8
                  SORT                             UNIQUE                                       1    8    9
                    UNION-ALL                                                                   1    9   10
                      INDEX                        RANGE SCAN           GROUPE_PK               1   10   11
                      NESTED LOOPS                                                              2   10   12
                        TABLE ACCESS               BY ROWID             UTILISATEURS            1   12   13
                          INDEX                    UNIQUE SCAN          UTILISATEUR_PK          1   13   14
                        TABLE ACCESS               BY ROWID             SERVICES                2   12   15
                          INDEX                    RANGE SCAN           SERVICE_PK              1   15   16
                TABLE ACCESS                       BY ROWID             MVT_ECRITURES           2    7   17
                  INDEX                            RANGE SCAN           I_MVT_ECR_ECR_MOUVEM    1   17   18
              TABLE ACCESS                         BY ROWID             COMPTES                 2    6   19
                INDEX                              UNIQUE SCAN          COMPTE_PK               1   19   20
 
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
          TABLE ACCESS                             BY ROWID             TIERS                   2    4   21
            INDEX                                  UNIQUE SCAN          TIERS_PK                1   21   22
      SORT                                         JOIN                                         2    2   23
        VIEW                                                            V_MVT_ECRITURE_COMME    1   23   24
          NESTED LOOPS                                                                          1   24   25
            VIEW                                                                                1   25   26
              SORT                                 UNIQUE                                       1   26   27
                UNION-ALL                                                                       1   27   28
                  INDEX                            RANGE SCAN           GROUPE_PK               1   28   29
                  NESTED LOOPS                                                                  2   28   30
                    TABLE ACCESS                   BY ROWID             UTILISATEURS            1   30   31
                      INDEX                        UNIQUE SCAN          UTILISATEUR_PK          1   31   32
                    TABLE ACCESS                   BY ROWID             SERVICES                2   30   33
                      INDEX                        RANGE SCAN           SERVICE_PK              1   33   34
            TABLE ACCESS                           BY ROWID             MVT_ECRITURES           2   25   35
              INDEX                                RANGE SCAN           I_MVT_ECR_SERVICE       1   35   36
 
37 ligne(s) sélectionnée(s).

Avec celle de remi4444 sans les INs celui-ci :

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
36
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             UTILISATEURS            1   18   19
                  INDEX                            UNIQUE SCAN          UTILISATEUR_PK          1   19   20
 
OPERATION                                          OPTIONS              OBJECT_NAME          POS. PA   ID   OPTIMIZER                 COST
-------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- ---------
                TABLE ACCESS                       BY ROWID             SERVICES                2   18   21
                  INDEX                            UNIQUE SCAN          SERVICE_PK              1   21   22
      INDEX                                        UNIQUE SCAN          GROUPE_PK               2    2   23
      NESTED LOOPS                                                                              3    2   24
        TABLE ACCESS                               BY ROWID             UTILISATEURS            1   24   25
          INDEX                                    UNIQUE SCAN          UTILISATEUR_PK          1   25   26
        TABLE ACCESS                               BY ROWID             SERVICES                2   24   27
          INDEX                                    UNIQUE SCAN          SERVICE_PK              1   27   28
 
29 ligne(s) sélectionnée(s).

Petite précision : Le mode Rule est prérequis et aucune statistique n'est calculée, Production Oblige !

As-tu pu modifier ta requête en supprimant des IN ou des sous-requêtes ? Si oui, peux-tu nous poster le résultat ?

remi4444 a déjà bien nettoyé les INs, peut être reste-t-il encore les sous-requêtes. Mais là, il me semble difficile de les supprimer sans modifier la logique de sécurité en place !

Merci sur ce coup de votre aide !
Peut être d'autres pistes ?
Je suis prenneur
.