[Info][8i] Pb avec une requête

**nuke_y** · 08/01/2008, 18h57

Bonjour à tous.

Désolé de vous déranger avec un problème aussi simple mais je suis en train de me demander si je ne suis pas fou.

Soit 4 tables :
T0 : 100 lignes, 3 champs dont 1 clé primaire Id
T1 : 20 000 lignes, 5 champs dont le champ Id, pas de clé primaire, pas d'index
T2 : 30 000 lignes, 5 champs dont le champ Id, pas de clé primaire, pas d'index
T3 : 100 lignes, 5 champs dont le champ Id, pas de clé primaire, pas d'index

Le besoin fonctionnel est le suivant : filtrer T0 en ne prenant que les lignes dont Id existe dans les 3 autres tables.

En SQL pur on ferait :

Code :

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

SELECT Id from T0 WHERE EXISTS (SELECT 1 FROM T1 WHERE T1.Id = T0.Id) AND EXISTS (SELECT 1 FROM T2 WHERE T2.Id = T0.Id) And EXISTS (SELECT 1 FROM T3 WHERE T3.Id = T0.Id)

Mais je ne peux pas utiliser EXISTS (requête générée automatiquement) donc la solution adoptée était :

Code :

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

SELECT distinct Id from T0 WHERE T1.Id = T0.Id AND T2.Id = T0.Id AND T3.Id = T0.Id)

Cette solution a bien marché pendant un moment et brusquement la requête s'est mis à durer des heures.

Le plan d'exécution est affreux (des MERGE JOIN (CARTESIAN) entre T1, T2 et T3 puis des NESTED LOOP avec T0).

J'ai d'abord pensé que le problème venait que les statistiques n'étaient pas calculées (ce qui était le cas) donc on les a calculées mais le plan d'exécution n'a pas bougé.

La solution d'indexer les champs des tables T1, T2 et T3 n'est pas possible pour des raisons d'architecture (tables de travail droppées et re-créées à la volée, etc.).

2 solutions ont été trouvées :
La première, la plus propre pour moi :

Code :

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

SELECT distinct Id from T0, T1, T2, T3 WHERE T1.Id = T0.Id AND T2.Id = T1.Id AND T3.Id = T2.Id

La 2e, qui me fait halluciner parce qu'effectivement les temps redeviennent normaux (je n'ai pas le plan d'exécution, peut-être demain)

Code :

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

SELECT distinct Id from T0, T1, T2, T3 WHERE T1.Id = T0.Id AND T2.Id = T0.Id AND T3.Id = T0.Id AND T2.Id = T1.Id AND T3.Id = T2.Id AND T1.Id = T3.Id

M'enfin moi ça me fait halluciner ce genre de comportement... Mais c'est peut-être courant avec des tables non indexées...

EDIT : je suis un abruti qui ne se relit pas je suis un abruti qui ne se relit pas je suis un abruti qui ne se relit pas je suis un abruti qui ne se relit pas je suis un abruti qui ne se relit pas je suis un abruti qui ne se relit pas je suis un abruti qui ne se relit pas ...

**phili_b** · 08/01/2008, 19h11

Je me demande en quoi des tables générées à la volée ne pourrait pas être indexées.
J'imagine que tes requêtes d'exemples sont FROM T1,T2,T3 et non FROM T1 tout seul sans autre table.

Sinon c'est quoi l'outil qui génère ces requêtes ? C'est un ETL ? Y'a pas moyen d'y paramétrer des index ?

Je ne comprendrais jamais qu'on impose des solutions foireuses.

**Michel SALAIS** · 08/01/2008, 19h16

La clause FROM n'est pas bonne. Toutes les tables référencées doivent y être ...
En m'opposant entièrement aux choix effectués dans cette application, je signale que l'optimiseur Oracle n'est pas capable de faire la fermeture transitive entre les colonnes et il peut en bénéficier si on lui ajoute ça à la main ...

En gros si c1 = c2 and c2 = c3 où c1, c2 et c3 sont des colonnes alors il est bénéfique d'y ajouter c1 = c3 !

**nuke_y** · 08/01/2008, 21h35

Désolé j'ai édité pour rajouter les tables manquantes dans les requêtes de solution.

Intéressant ce que tu dis Michel. Tu confirmes que rajouter des jointures "en trop" peut servir à Oracle (enfin ça je viens de m'en apercevoir aujourd'hui en fait) ? Mais surtout que ce n'est pas aberrant de songer à le faire ? Ou est-ce que c'est une bidouille/astuce couramment utilisée mais qui est globalement déconseillée.

Sinon l'ETL c'est Genio. Il permet d'indexer des tables construites à la volée mais c'est interdit par les normes de développement de mon client. Personnellement, chez un autre client je n'utilise QUE des tables que je construit moi même et donc que je peux indexer moi-même. Mais sans rentrer dans le débat, je comprend un peu mon client. Une table construite à la volée, s'il faut à la volée aussi l'indexer et calculer les stats, ça devient lourd à gérer et c'est source d'erreur. En gros ils préfèrent que ce soit moins performant tout le temps mais fiable et robuste. Mon problème c'est :

Puisque on joue à celui qui saura le mieux parler à l'optimiseur sans mettre en place l'architecture qui garantira un plan d'exécution constant et des perfs constantes, est-ce que la moindre modification sur la base ne rendra pas la 1er nouvelle solution aussi peu performante que l'ancienne solution ?

**Michel SALAIS** · 09/01/2008, 21h50

Envoyé par nuke_y

Intéressant ce que tu dis Michel. Tu confirmes que rajouter des jointures "en trop" peut servir à Oracle (enfin ça je viens de m'en apercevoir aujourd'hui en fait) ? Mais surtout que ce n'est pas aberrant de songer à le faire ? Ou est-ce que c'est une bidouille/astuce couramment utilisée mais qui est globalement déconseillée.

Personnellement, je trouves que c'est toujours utile en attendant que l'optimiseur fasse la fermeture transitive quand l'égalité concerne des colonnes ...

J'ajoute que je n'aime l'idée qu'un logiciel ne trouve comme solution que de créer des tables à la volée avec Oracle ...