Vue récalcitrante 7.3.4

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 :

Citation:

---

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

---

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

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
.

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 ?

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

Code:

---

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.

Alors voilà la situation :

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

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:

---

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:

---

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 !

Citation:

---

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
.

pour les sous requêtes, essaye peut-être avec un UNION ALL plutôt qu'un OR...

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

WHERE EXISTS ( SELECT 1 FROM groupes grp WHERE me.service_fk = grp.service_fk AND grp.operateur_fk = USER UNION ALL 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 )

---

D'abord je ne vois plus trop le rapport avec la requete de départ.... 8O

ensuite si tu es en mode rule, il faut que tu te pose la question des ordres d'apparition de tes tables et de tes jointures, c'est vraiment une démarche particulière....

Citation:

---

Envoyé par Yorglaa

pour les sous requêtes, essaye peut-être avec un UNION ALL plutôt qu'un OR...

---

Pas d'accord pour ce cas précis, mais ça se teste....

Citation:

---

D'abord je ne vois plus trop le rapport avec la requete de départ....

---

Non la requête que je donne dans mon dernier post est celle que j'utilise pour interroger ma vue.
La définition de ma vue est contenue dans le post initial.

Citation:

---

il faut que tu te pose la question des ordres d'apparition de tes tables et de tes jointures

---

Effectivement, je prend bien en compte ces points à chaque fois.

Avant de vous donner le plan d'exécution de ma requête avec la vue de Yorglaa, je vous montre celui sans passer par une vue (donc en accédant directement à la table mvt_ecritures) :

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

---

Ce serait éventuellement ce plan d'exécution que je souhaite obtenir au travers de ma vue.

Maintenant la plan d'exécution avec la vue de Yorglaa. Je l'ai modifié légèrement au niveau de la condition de corélation pour que ça marche convenablement :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

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 UNION ALL SELECT 1 FROM services ser, utilisateurs utl WHERE me.service_fk = ser.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')

---

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

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

---

Pas terrible !
Comment faire ?
.

Dans le 2ème Select du UNION ALL, ajoute cette clause :

Code:

---

AND me.service_fk LIKE utl.service_fk || '%'

---

Pose toi la question de ce que tu ferais toi si tu devais écrire un algo d'accés rapide à tes données, quelles sont les critères les plus discriminants par lesquels tu commencerais, quelles sont les colonnes de jointures tu utiliserais etc... ainsi tu pourras déduire les index à créer ou les jointures à forcer.

Je continue à penser qu'il est très périlleux de mettre un "UNION ALL" à l'interrieur d'une vue qui est accédée de manière complexe dans une autre requête, surtout par des jointures externes. Oracle à vite fait de résoudre ce cas en la chargeant entièrement dans son espace temporaire et donc faire une "acces full" sur au moins une des tables qui la compose.

Le fait de faire des "EXISTS" discipline et simplifie les choses car la requête de base est "SELECT * FROM mvt_ecritures ...." puis il y a des critère de filtres simples, donc oracle n'a pas à se poser de question pour choisir les bonnes tables à acceder en 1ier. Il faut bien savoir que bien souvent on fait des jointures par paresse d'écriture alors que ce qu'on veux faire vraiment est du filtre (donc de l'EXISTS).

Sur ce que je vois de la requête (ce n'est qu'une impression extérieure):

Le problème d'après le plan est cet accès FULL à la table MVT_ECRITURES

Le critère de départ discriminant à l'air d'etre:

Code:

---

1 2 3

M_EC_ORIGINES.SEQ_MVT_ECRITURE_ID = 39164 and M_EC_ORIGINES.SEQ_PIECE_ECRITURE_FK = 10772 and

---

Donc il faut s'assurer que ces colonnes soient bien indexées (soit le couple, soit dans un ensemble dont toutes les colonnes précédentes sont dans requête, par exemple un triplet TYPE_JOURNAL_FK,SEQ_MVT_ECRITURE_ID, SEQ_PIECE_ECRITURE_FK ç'est bon). A priori le nom SEQ_MVT_ECRITURE_ID laisse à penser que c'est la clef primaire donc indexée...

Ensuite, il y a une jointure externe vers la meme vue (le cocktail vue/jointure externe est particulièrement problématique pour les questions d'optim).

cette jointure se fait sur:

Code:

---

1 2 3 4

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...

---

Le noeud du problème est sur cette triplette SEQ_MVT_ORIGINE_FK , SEQ_PIECE_ORIGINE_FK , EXERCICE_ORIGINE_FK.

Il faut que ce triplet soit indexé ou au mois qu'une ou 2 de ces colonnes dont toi seul peut savoir si elles sont discriminantes ou pas le soient.

Voilà ce n'est qu'une illustration de la démarche à avoir, à toi de voir si d'autres tables peuvent poser problème.

Ce que tu peux faire, c'est peut etre analyser de manière grossiere toutes les tables en présence (estimate 10000 rows par exemple), tu ne risque rien puisque ta base est en mode rule. Puis effectue la requête avec le HINT /*+ FIRST_ROWS */ et si ça va vite, regarde le plan d'exécution, ça te donnera peut etre des idées.

Pour finir ça ne me semble pas si abhérant que ça de devoir faire une requête shuntant la vue à des fin d'optim, ça se voit souvent.....

bon courage!

Citation:

---

il est très périlleux de mettre un "UNION ALL" à l'interrieur d'une vue qui est accédée de manière complexe dans une autre requête, surtout par des jointures externes.

---

UNION ALL ou OR avec EXISTS n'aide ni l'un ni l'autre à l'amélioration du plan d'exécution de ma requête au travers de ma vue, on le constate comme précédemment !

Citation:

---

Le fait de faire des "EXISTS" discipline et simplifie les choses car la requête de base est "SELECT * FROM mvt_ecritures ...." puis il y a des critère de filtres simples, donc oracle n'a pas à se poser de question pour choisir les bonnes tables à acceder en 1ier.

---

Je veux bien, mais ce n'est pas ce que je constate au vu du plan d'exécution avec EXISTS et OR ?

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

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

---

Pas terrible non plus !

En soit, ma requête ne me pose pas de problème de performance lorsqu'elle est appliquée à la table directement (en by passant la vue). Le plan d'exécution est parfait à mon sens et m'indique que ma requête est bien définie. Les bons indexes sont utilisés par l'optimiseur me semble-t-il, même pour les jointures externes.

Citation:

---

analyser de manière grossiere toutes les tables en présence ... effectue la requête avec le HINT /*+ FIRST_ROWS */ et si ça va vite, regarde le plan d'exécution, ça te donnera peut etre des idées.

---

Je vais tester pour voir dans un 2ème temps.

Citation:

---

Pour finir ça ne me semble pas si abhérant que ça de devoir faire une requête shuntant la vue à des fin d'optim, ça se voit souvent

---

En fait, j'ai quelques vues comme celle que je présente, qui sont bâties sur la même logique de sécurité d'accès (par service). Ces vues agissent comme des filtres lors des accès utilisateur aux tables sous-jacentes.
Donc, toutes les requêtes utilisateur doivent se faire au travers de mes vues. Les shunter reviendrait à ne plus avoir de sécurité !
Petite précision sur les accès utilisateur : Ils peuvent se faire au moyen d'applications Forms, Reports, BusinessObjects, Excel et j'en passe.
Il est primordial que je puisse optimiser mes vues et obtenir de bons temps de réponse.

Mais là, je commence à me sentir mal, comment puis-je faire ?
Merci de m'aider !
.

Je ne dis pas qu'il suffise de mettre un EXISTS pour que tout se resolve par miracle, simplement si ta vue sert à filtrer des données, alors il faut utiliser l'instruction SQL de filtrage qui est précisément le EXISTS. Ce n'est pas uniquement une question de principe, c'est parceque en faisant ça, il n'y a pas d'accès inutiles, il n'y a pas de problèmes de doublons qui apparaissent avec des jointures etc... avec un IN portant sur une jointure, il peut arriver que le sous ensemble ramène des milions de lignes sans qu'on s'en aperçoive....

J'irais meme plus loin dans son utilisation pour la définition de ta vue:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22

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 = utl.service_id AND EXISTS ( SELECT 1 from 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')

---

ça a l'air plus compliqué à écrire mais en fait c'est plus simple à lire parce qu'on vois bien la descente de table en table pour le filtrage.

Code:

---

1 2 3

mvt_ecritures -> groupes -> services -> utilisateurs

---

Si ta vue doit être utilisée partout alors c'est ce chemin qu'il faudra qu'elle emprunte, il ne faut pas qu'oracle s'amuse à commencer par filtrer les utilisateurs, puis les services, puis les écritures associées pour ensuite croiser avec le reste car le volume d'écriture serait trop important. Mais ça, c'est évident pour celui qui connait l'application mais pas pour oracle car lui est en mode RULE donc sans statistique donc il n'a aucune idée de la répartition des données entre les tables.

Or il y a une particularité à la jointure utilisateurs <-> services, c'est qu'elle se fait par un LIKE signifiant que tu veux que tes colonnes de ta table SERVICES commencent par les identifiant présents dans UTILISATEURS pour se finir par n'importe quoi d'autre. De ce fait, tu indiques que tu commences par regarder la table UTILISATEURS puis tu vas regarder par les index de la table SERVICES ce qui commence bien comme tu veux. Tu as donc forcé par cette jointure le chemin:

Code:

---

UTILISATEURS -> SERVICES -> MVT_ECRITURES

---

d'autre part, la jointure externe de ta requête force le chemin :

Code:

---

1 2 3 4

ORIGINE (vue) -> TIERS -> COMPTES -> EMARGEANT (vue)

---

Il y a donc 2 chemins en sens inverses qui se rejoignent, oracle n'as donc d'autre choix que de faire un acces FULL à ta table d'écritures pour faire le lien.

Pour vérifier cette thèse, tu devrais jouer sur cette partie de ta vue faisant le lien services <-> utilisateurs, en la commentant pour commencer. A la limite si le LIKE n'est qu'une astuce pour forcer le sens de la jointure, moi je le mettrait dans l'autre sens! si ce LIKE est indispensable, alors à mon avis ça pose un gros problème général à toute la base, il faut absoluement réussir à fabriquer une colonne qui aura les identifiants exacts et qui permettront des jointures simples entre ces 2 tables.

Evidement, sans avoir la base à coté, je ne peux que donner des impressions et des principes. Il est possible que tu doives tatonner encore un peu tout seul. Une méthode est de commenter des morceaux, ou de forcer le sens de jointures dans ta vue et regarder le resultat jusqu'à ce que tu n'ai plus d'access FULL à ta table mvt_ecritures.

Pour forcer le sens des jointure, il y a le LIKE, il y a aussi les methodes laides d'ajout de 0 à une colonne de type nombre ((table1.id + 0) = table2.id) et de concatenation avec '' pour les colonnes de type caractère ((table1.clef || '') = table2.clef). Il y a enfin la methode plus propre des HINTS (/*+ USE_NL (TABLE1,TABLE2) */ ou /*+ ORDERED */).

Remarque générale sur le contexte que tu décris:
Il me semble totalement contradictoire d'avoir une base en mode RULE et d'y autoriser des accés par BO ou autres outils du genre, car qui dit mode RULE dit maitrise totale de toutes les requêtes (ordre d'apparition des tables des jointures etc...) or BO se moque comme de la guigne de l'ordre dans lequel il organise ses requêtes dynamiques, j'ai donc bien peur qu'on courre à la catastrophe avec ce mélange détonnant... Attention avec ce mode par exemple, il arrive que la création d'un index (acte apparament anodin) bouzille completement les plans d'exécution car pour oracle passer par un index est à priori toujours une bonne chose en mode RULE or dans les faits, ce n'est pas toujours le cas...

le mode RULE est plutot utilisé pour stabiliser un batch ou un module de calcul complexe, mais il peut etre activé au niveau de la session du module seulement.

bon courage...

Citation:

---

Dans le 2ème Select du UNION ALL, ajoute cette clause :
Code :
AND me.service_fk LIKE utl.service_fk || '%'

---

Pour réponde à Yorglaa, je te montre l'explain plan en ajoutant ce que tu propose :

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

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

---

AND me.service_fk LIKE utl.service_fk || '%'[/QUOTE]

Rien a changé, plan d'exécution identique !

Il faut que j'explique le mécanisme :
En fait, j'ai une table des utilisateurs UTILISATEURS et une table GROUPES qui contient les services auxquels les utilisteurs ont eventuellement accès en plus de leurs services propres ou de ceux de leurs divisions.
En même temps dans la table des utilisateurs, j'ai une colonne service et une colonne division. Ces 2 colonnes me permettent d'indiquer le service et la division dont l'utilisateur fait parti.
La colonne service peut ou non être renseignée :
Si elle est NULL, cela veut dire qu'il a accès à tous les services de sa division plus ceux renseignés dans la table GROUPES.
Si elle est NOT NULL, il a accès à son service uniquement plus ceux renseignés dans la table GROUPES.
A côté de ça, si la colonne divison est renseignée, cela veut dire que l'utilisateur à accès uniquement à son service si la colonne service est NOT NULL, sinon, il a accès à tous les service de sa division.
Si la colonne division est NULL, il a accès à toutes les divisions, donc à tous les services de toutes les divisions.

Pour revenir à ce que tu propose Yorglaa,

Citation:

---

AND me.service_fk LIKE utl.service_fk || '%'

---

Elle n'apporte rien de plus car je dois obligatoirement passer par la table SERVICES pour obtenir la liste des services dont l'utilisateur à accès.


Merci X pour tes explications qui sont très intêressantes et claires. Je suis d'accord avec toi sur tous les points.

Ta dernière proposition donne ce qui suit. Je l'ai modifiée légèrement au niveau de la condition de corélation pour que ça marche convenablement :

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

---

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

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 BY ROWID UTILISATEURS 2 18 21 INDEX UNIQUE SCAN UTILISATEUR_PK 1 21 22 INDEX UNIQUE SCAN GROUPE_PK 2 2 23 NESTED LOOPS 3 2 24 TABLE ACCESS BY ROWID SERVICES 1 24 25 INDEX UNIQUE SCAN SERVICE_PK 1 25 26 TABLE ACCESS BY ROWID UTILISATEURS 2 24 27 INDEX UNIQUE SCAN UTILISATEUR_PK 1 27 28   29 ligne(s) sélectionnée(s).

---

L'explain plan a changé mais il y a toutjous un FULL TABLE SCAN sur la table MVT_ECRITURES.
Exécution pas terrible, mais il retourne bien le bon résultat !

Citation:

---

Si ta vue doit être utilisée partout alors c'est ce chemin qu'il faudra qu'elle emprunte, il ne faut pas qu'oracle s'amuse à commencer par filtrer les utilisateurs, puis les services, puis les écritures associées pour ensuite croiser avec le reste car le volume d'écriture serait trop important.

---

Oracle le fait bien dans le bon sens si je lis correctement le plan d'exécution, mais il se trompe sur le mode d'accès.

Citation:

---

Mais ça, c'est évident pour celui qui connait l'application mais pas pour oracle car lui est en mode RULE donc sans statistique donc il n'a aucune idée de la répartition des données entre les tables.

---

Citation:

---

Il y a donc 2 chemins en sens inverses qui se rejoignent, oracle n'as donc d'autre choix que de faire un acces FULL à ta table d'écritures pour faire le lien.

---

Comment pourrais-je lui indiquer comment il doit procéder ?

Citation:

---

si ce LIKE est indispensable, alors à mon avis ça pose un gros problème général à toute la base, il faut absoluement réussir à fabriquer une colonne qui aura les identifiants exacts et qui permettront des jointures simples entre ces 2 tables.

---

Comme je l'expliquais plus haut, la logique de sécurité m'oblige à procéder de la sorte, j'ai bien peur que je ne puisse faire autrement.

Citation:

---

Il y a enfin la methode plus propre des HINTS (/*+ USE_NL (TABLE1,TABLE2) */ ou /*+ ORDERED */).

---

J'essaierai dans un troisième temps lorsque j'aurai épuisé toutes les possibilités sans avoir à recourrir aux HINT donc au CBO.

Citation:

---

BO se moque comme de la guigne de l'ordre dans lequel il organise ses requêtes dynamiques

---

Il est toujours possible avec BO de forcer la requête générée en la figeant ou bien en mettant en oeuvre les poids des tables. Ce qui permet de fixer l'ordre d'apparition des tables et donc l'ordre des jointures. De ce fait, je ne serais pas aussi affirmatif !

Citation:

---

Une méthode est de commenter des morceaux, ou de forcer le sens de jointures dans ta vue et regarder le resultat jusqu'à ce que tu n'ai plus d'access FULL à ta table mvt_ecritures.

---

Sans en venir tout de suite aux HINTS, auriez-vous éventuellement d'autres propositions pour la définition de ma vue ?
J'ai commenté des parties, essayé les idées de remi4444 et Yorglaa et d'autres.
Je suis en manque d'idée !

Merci d'avance de votre aide
.

Citation:

---

Envoyé par star

Il est toujours possible avec BO de forcer la requête générée en la figeant ou bien en mettant en oeuvre les poids des tables. Ce qui permet de fixer l'ordre d'apparition des tables et donc l'ordre des jointures. De ce fait, je ne serais pas aussi affirmatif !

---

Admettons que tu puisses arriver meme avec BO à trouver le bon ordre de tes requêtes, je trouve néamoins que ce n'est pas du tout dans l'esprit d'une base à acces "souple". Une base en mode RULE est une base par laquelle tu n'accède qu'avec des requêtes parfaitement maitrisées de A à Z, donc ça représente un gros travail à chaque fois, mais après tout ce n'est qu'un avis...

Citation:

---

Envoyé par star

Oracle le fait bien dans le bon sens si je lis correctement le plan d'exécution, mais il se trompe sur le mode d'accès.

---

Je ne suis pas d'accord on voit au contraire qu'il y a au plus profond de la boucle un accès par la pk de la table UTILISATEURS, donc précisément le mauvais sens pour moi... tu peux toujours essayer de faire le test en remplaçant l'égalité "utl.operateur_id = USER" par "utl.operateur_id = USER||''" juste histoire de disqualifier l'index.

Est-ce que tu a fait le test de remplacer les LIKE par des égalité simple (meme si je sais que ça va pas donner les bons resultats) juste pour voir si oracle trouve un autre chemin ?
est-ce que tu peux faire la combinaisons des 2 trucs ?

De manière générale je pense qu'il faut absoluement que tu trouves un moyen d'éviter ce genre de jointure dans une vue qui va resservir partout parceque meme si au prix d'attroces souffrances, tu arrives à t'en sortir avec cette requête ci, tu risques de retomber dans les memes problèmes en permanance. Donc soit tu réécrit toute tes conditions de droits avec d'autre "OR" et "EXISTS", soit peut etre faut-il envisager de passer par des vue matérialisées (ou de simples tables remplies au moment de l'affectation de droits) afin d'arriver à avoir un accès simple.

Honnetement je vois le genre de gestion de droits auquel tu es confronté... le fait de faire porter des significations à la nullité ou non des colonnes entraine des requêtes trop alambiquées, ça passe quand ça ne porte pas sur de gros volumes, mais là c'est tres dur de s'en sortir. Rajouté à cela que tu n'utilises pas le CBO qui pourrait eventuellement trouver une solution moins couteuse (ce qui n'est pas garanti non plus), là c'est franchement mission impossible à mon avis.
J'ai déja été confronté à ce genre de chose et la solution à été de faire des consolidations àfin de mettre à plat certaines correspondances pour éviter de faire porter une signification "globalisante" à la présence de null. Dans l'esprit d'oracle si tu mets "NULL" dans ta colonne services_fk c'est que l'utilisateur n'a acces à aucun service alors que chez toi ça signifie qu'il a acces à tous...

Parenthèse
De plus je n'aime pas trop l'astuce du LIKE pour traiter ça (ça va dans un programme PL/SQL mais là c'est beaucoup plus lourd de conséquences). Je verrais plutot une jointure du genre (me.service_fk = utl.service_fk or utl.service_fk is null) qui es la traduction plus exacte de ta règle. C'est d'ailleur un autre avantage du EXISTS c'est que tu peux te permetre ce genre de chose sans craindre les produits cartésiens puisqu'il s'arrete à la premier occurence trouvée si l'existance est vérifiée.
Fin de parenthèse

Est-ce que tu ne pourrais pas envisager de faire des tables de "consolidation" qui te déclinerais toutes tes correspondances utilisateur/service et utilisateurs/divisions ?

Citation:

---

tu peux toujours essayer de faire le test en remplaçant l'égalité "utl.operateur_id = USER" par "utl.operateur_id = USER||''" juste histoire de disqualifier l'index.
Est-ce que tu a fait le test de remplacer les LIKE par des égalité simple (meme si je sais que ça va pas donner les bons resultats) juste pour voir si oracle trouve un autre chemin ?
est-ce que tu peux faire la combinaisons des 2 trucs ?
Je verrais plutot une jointure du genre (me.service_fk = utl.service_fk or utl.service_fk is null) qui es la traduction plus exacte de ta règle.

---

Si je fais un mixte de tout ça, cela donne :

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 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 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63

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 CONCATENATION 3 15 18 NESTED LOOPS 1 18 19 TABLE ACCESS BY ROWID SERVICES 1 19 20   OPERATION OPTIONS OBJECT_NAME POS. PA ID OPTIMIZER COST -------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- --------- INDEX UNIQUE SCAN SERVICE_PK 1 20 21 TABLE ACCESS BY ROWID UTILISATEURS 2 19 22 INDEX UNIQUE SCAN UTILISATEUR_PK 1 22 23 MERGE JOIN 2 18 24 TABLE ACCESS BY ROWID SERVICES 1 24 25 INDEX UNIQUE SCAN SERVICE_PK 1 25 26 FILTER 2 24 27 TABLE ACCESS FULL UTILISATEURS 1 27 28 MERGE JOIN 3 18 29 TABLE ACCESS BY ROWID SERVICES 1 29 30 INDEX UNIQUE SCAN SERVICE_PK 1 30 31 FILTER 2 29 32 FILTER 1 32 33 TABLE ACCESS FULL UTILISATEURS 1 33 34 INDEX UNIQUE SCAN GROUPE_PK 2 2 35 CONCATENATION 3 2 36 NESTED LOOPS 1 36 37 TABLE ACCESS BY ROWID SERVICES 1 37 38 INDEX UNIQUE SCAN SERVICE_PK 1 38 39 TABLE ACCESS BY ROWID UTILISATEURS 2 37 40 INDEX UNIQUE SCAN UTILISATEUR_PK 1 40 41   OPERATION OPTIONS OBJECT_NAME POS. PA ID OPTIMIZER COST -------------------------------------------------- -------------------- -------------------- ---- ---- ---- -------------------- --------- MERGE JOIN 2 36 42 TABLE ACCESS BY ROWID SERVICES 1 42 43 INDEX UNIQUE SCAN SERVICE_PK 1 43 44 FILTER 2 42 45 TABLE ACCESS FULL UTILISATEURS 1 45 46 MERGE JOIN 3 36 47 TABLE ACCESS BY ROWID SERVICES 1 47 48 INDEX UNIQUE SCAN SERVICE_PK 1 48 49 FILTER 2 47 50 FILTER 1 50 51 TABLE ACCESS FULL UTILISATEURS 1 51 52   53 ligne(s) sélectionnée(s).

---

J'ai l'impression que c'est pire !

Citation:

---

...passer par des vue matérialisées (ou de simples tables remplies au moment de l'affectation de droits) afin d'arriver à avoir un accès simple.
...faire des consolidations àfin de mettre à plat certaines correspondances pour éviter de faire porter une signification "globalisante" à la présence de null.
Est-ce que tu ne pourrais pas envisager de faire des tables de "consolidation" qui te déclinerais toutes tes correspondances utilisateur/service et utilisateurs/divisions ?

---

Dans ce cas, pour le test, je change ma vue pour simuler une table de correspondances en gardant l'acces à GROUPES et en le réutilisant pour la table de correspondances :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16

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 groupes grp WHERE me.service_fk = grp.service_fk AND grp.operateur_fk = 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

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 3 15 18 INDEX UNIQUE SCAN GROUPE_PK 2 2 19 INDEX UNIQUE SCAN GROUPE_PK 3 2 20   21 ligne(s) sélectionnée(s).

---

Tjrs FULL TABLE SCAN sur MVT_ECRITURES, pas de chance !

Je réduit avec qu'un seul EXISTS :

Code:

---

1 2 3 4 5 6 7 8 9 10

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

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

---

Tjrs pas de chance !

Bon, entre temps j'ai pensé à transformer ma vue en une jointure :

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

select me.* 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 ) ) lis, mvt_ecritures me where lis.svce = me.service_fk 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')

---

Cela donne :

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

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

---

Maintenant, je n'ai plus de FULL TABLE SCAN sauf que les temps de réponse restent longs, je présume que l'accès par l'indexe I_MVT_ECR_SERVICE en est pour quelque chose.

Peut-être d'autre suggestions ?
.

avec ta vue originale (celle avec les EXISTS), as-tu essayé avec le hint

Code:

---

/*+ push_subq */

---

afin de privilégier DANS UN PREMIER TEMPS les filtres induits avec les sous Select ?
éventuellement à tester aussi avec le EXISTS en UNION ALL...

Plusieurs remarques...

D'abord, je te présente toutes mes confuses je me suis trompé de sens pour la méthode qui discalifie un index, en fait c'est "utl.operateur_id||'' = USER" et non l'inverse :oops: mais ce n'était qu'un truc pour tester, c'est quand meme pas idéal d'en arriver là...

Sur la vue avec les EXISTS, tu as oublié des parenthèses, donc pour récapituler ça donnerais:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18

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 division_fk is null) AND (ser.service_id = utl.service_fk or utl.service_fk is null) AND utl.operateur_id||'' = USER) ) )

---

Sur la démarche générale, je pense qu'il faudrait faire de manière plus progressive, ici il y a trop de paramètres au problème et le tatonnage porte sur trop de cas à essayer.
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.

Séparer la vue en blocs est effectivement une bonne idée mais attention, il ne faut pas faire d'UNION mais des UNION ALL avec des conditions s'excluant les unes les autres pour éviter les doublon. L'union fait un dédoublonnage implicite donc un tri, ce qui est couteux dans ton dernier plan.

sur ton dernier plan d'exécution, il n'y a pas d'acces FULL mais par un index qui ne doit pas etre trés discriminant donc ça reviens un peu au meme... le problème avec le RBO c'est que tous les indexs ont le même poids...

Personnellement j'essayerais d'insister sur une construction d'une table qui regroupe toute les correspondance. 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....
D'autre part le plan accede par la PK de la table groupe (ce qui est peut etre normal en RBO) mais semble éliminer un chemin qui passerais par un index de FK, est-ce que la colonne grp.service_fk est bien indexée ??

à la limite j'essayerais juste pour le test de faire la vue suivante:

Code:

---

1 2 3 4 5 6 7 8 9

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

---

Juste pour voir si le chemin est faisable...

En réfléchissant bien à la problématique, je me rends compte que c'est pas simple.... en effet, il y a des accès qui dépendent directement de droits affectés aux utlisisateurs, à savoir s'ils ont null ou pas dans une des colonnes de la table ce qui leur ouvre ou restreignent des vues sur les écritures. 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.

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19

select * from mvt_ecritures where ( select count(1) from utilisateurs where operateur_id = USER and service_fk is null and division_fk is null) = 1 AND .... UNION ALL select * from mvt_ecritures where ( select count(1) from utilisateurs where operateur_id = USER and service_fk is null and division_fk is not null) = 1 AND .... UNION ALL select * from mvt_ecritures where ( select count(1) from utilisateurs where operateur_id = USER and service_fk is not null and division_fk is null) = 1 AND .... UNION ALL select * from mvt_ecritures where ( select count(1) from utilisateurs where operateur_id = USER and service_fk is not null and division_fk is not null) = 1 AND ....

---

Certes ça peut paraitre un peu alambiqué... mais l'avantage est que tu peux tester avec des vues intemédiaires ne comportant que certaine partie des blocs.

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.

Cette fonction pourrais prendre comme paramètres le service et le user (voir le compte mais c'est peut etre pas la peine). Dans la fonction du tu calcurerais ton acces en revoyant 1 ou 0 selon que les droits sont ouverts ou pas, la vue serais donc:

Code:

---

1 2 3 4 5

SELECT me.* FROM mvt_ecritures me WHERE (LA_FONCTION (USER,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')

---

Toujours pareil, il faut tester progressivement c'est à dire sans les "NOT IN" dans un premier temps...

Il est peut etre possible d'optimiser le nombre d'appel à la fonction (bien que le mode DETERMINISTIC serve déja à ça), en passant par une table de référence des services.

Code:

---

1 2 3 4 5 6 7 8

SELECT me.* FROM mvt_ecritures me WHERE EXISTS ( select 1 from SERVICES srv where me.service_fk = srv.service_pk and LA_FONCTION (USER,srv.service_pk) = 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')

---

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.

La vue deviendrait donc:

Code:

---

1 2 3 4 5 6 7 8

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

---

J'essaye la suggestion de Yorglaa, ça donne avec la vue de remi4444 :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT /*+ push_subq */ 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 = ser.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')

---

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

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

---

Avec ta vue Yorglaa :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17

SELECT /*+ push_subq */ me.* FROM mvt_ecritures me WHERE EXISTS ( SELECT 1 FROM groupes grp WHERE me.service_fk = grp.service_fk AND grp.operateur_fk = USER UNION ALL SELECT 1 FROM services ser, utilisateurs utl WHERE me.service_fk = ser.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')

---

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

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

---

Avec les 2 vues, je débouche sur un FULL TABLE SCAN.
.

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

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 !
.