Oracle Joins-comparaison entre la syntaxe conventionnelle et la syntaxe ANSI
préambule
dernièrement, je vois trop de geeks commentant les questions D'Oracle disant que "N'utilisez pas ( + ) l'opérateur, utilisez plutôt la syntaxe de jointure".
Question
je vois que les deux fonctionnent bien. Mais quelle est la différence réelle entre les utiliser et qu'est-ce qui vous fait sentir l'utiliser? J'apprécierais des réponses, plus basées sur l'expérience.
1. Is there anything to do with limitations in application, performance,
etc. while using them?
2. What would you suggest for me?
I a lire quelque chose sur documentation Oracle , mais pas assez bon pour me faire comprendre ou de se sentir à l'aise avec l'information complète.
Note : je prévois de migrer plus de 200 paquets et procédures, si le mot-clé doit être utilisé à la place de ( + )
3. Also is there any freeware tools to do the rewrite?
remise d'échantillons
┌───────────────────────────────────┬─────────────────────────────────────────────┐
│ INNER JOIN - CONVENTIONAL │ INNER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ emp.deptno │ ename, │
│ FROM │ dname, │
│ emp, │ emp.deptno, │
│ dept │ dept.deptno │
│ WHERE │ FROM │
│ emp.deptno = dept.deptno; │ scott.emp INNER JOIN scott.dept │
│ │ ON emp.deptno = dept.deptno; │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ LEFT OUTER JOIN - CONVENTIONAL │ LEFT OUTER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ emp.deptno │ ename, │
│ FROM │ dname, │
│ emp, │ emp.deptno, │
│ dept │ dept.deptno │
│ WHERE │ FROM │
│ emp.deptno = dept.deptno(+); │ scott.emp LEFT OUTER JOIN scott.dept │
│ │ ON emp.deptno = dept.deptno; │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ RIGHT OUTER JOIN - CONVENTIONAL │ RIGHT OUTER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ emp.deptno │ ename, │
│ FROM │ dname, │
│ emp, │ emp.deptno, │
│ dept │ dept.deptno │
│ WHERE │ FROM │
│ emp.deptno(+) = dept.deptno; │ scott.emp RIGHT OUTER JOIN scott.dept │
│ │ ON emp.deptno = dept.deptno; │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ FULL OUTER JOIN - CONVENTIONAL │ FULL OUTER JOIN - ANSI SYNTAX │
├───────────────────────────────────┼─────────────────────────────────────────────┤
│ SELECT │ SELECT │
│ * │ * │
│ FROM │ FROM │
│ emp, │ scott.emp FULL OUTER JOIN scott.dept │
│ dept │ ON emp.deptno = dept.deptno; │
│ WHERE │ │
│ emp.deptno = dept.deptno(+) │ │
│ UNION ALL │ │
│ SELECT │ │
│ * │ │
│ FROM │ │
│ emp, │ │
│ dept │ │
│ WHERE │ │
│ emp.deptno(+) = dept.deptno │ │
│ AND emp.deptno IS NULL; │ │
└───────────────────────────────────┴─────────────────────────────────────────────┘
PS : Lire la résumé des réponses pour toutes les mises à jour groupées.
10 réponses
si vos paquets de 200+ fonctionnent comme prévu avec la syntaxe" old fashioned", qu'il en soit ainsi.
SQL ne commencera pas à mieux fonctionner après la migration vers la syntaxe ANSI - c'est juste une syntaxe différente.
tout cela étant dit, la syntaxe ANSI est plus propre - vous n'allez pas à la jointure normale si vous oubliez (+) dans une jointure externe multi-colonnes.
dans le passé, il y avait quelques bogues avec la syntaxe ANSI, mais si vous allez avec le plus récent 11.2 ou 12.1 que doit être fixe déjà.
Bien sûr, vous connaissez mieux votre environnement et vos priorités - comme SchmitzIT L'a dit - la syntaxe ANSI fait partie de la norme SQL et cela aiderait quand vous utiliserez un autre produit RDBMS.
dans 11g vous devez utiliser la syntaxe de jointure ANSI. Elle est plus flexible (prise en charge des joints externes complets et des joints partitionnés), et comme le dit la documentation:
Oracle recommande fortement que vous utilisiez la syntaxe plus flexible de jointure de clause montrée dans le premier exemple.
C'est une raison suffisante.
Groupement des réponses avec
- utiliser des jointures explicites plutôt qu'implicites (qu'elles soient externes ou non) est qu'il est beaucoup plus facile de créer accidentellement un produit cartésien avec les jointures implicites. Avec les jointures explicites, vous ne pouvez pas en créer une" par accident". Plus il y a de tables impliquées, plus le risque est grand que vous manquiez une condition de jointure.
- essentiellement ( + ) est sévèrement limité par rapport à à L'ANSI se joint. De plus, il n'est disponible Qu'en Oracle alors que la syntaxe de jointure ANSI est supportée par tous les principaux SGBD
- SQL ne commencera pas à mieux fonctionner après la migration vers la syntaxe ANSI - c'est juste une syntaxe différente.
- Oracle recommande fortement d'utiliser la syntaxe de jointure de clause la plus flexible indiquée dans le premier exemple. Dans le passé, il y avait quelques bugs avec la syntaxe ANSI, mais si vous utilisez la dernière version 11.2 ou 12.1, cela devrait déjà être corrigé.
- en utilisant les opérateurs de jointure s'assurer que votre code SQL est conforme ANSI, et permettrait ainsi à une application front-end d'être plus facilement porté pour d'autres plates-formes de base de données. Les conditions D'assemblage
- ont une très faible sélectivité sur chaque table et une forte sélectivité sur les tuples dans le produit croisé théorique. Les Conditions dans la déclaration où ont généralement une sélectivité beaucoup plus élevée.
- Oracle convertit en interne la syntaxe ANSI en (+) syntaxe, vous pouvez voir cela se produire dans la section des informations de prédicat du plan d'exécution.
Écueil Possible en utilisant la syntaxe ANSI sur 12c moteur
incluant une possibilité de bug dans JOIN in 12c. Voir ici
SUIVI:
Quest SQL optimizer tool réécrit le SQL à la syntaxe ANSI.
mis à part les raisons mentionnées par d'autres, l'utilisation des opérateurs de jointure assure que votre code SQL est conforme ANSI, et permettrait ainsi à une application front-end d'être plus facilement porté pour d'autres plateformes de base de données.
Prédicats de séparation basés sur la sélectivité
séparer votre requête en conditions de jointure et où les conditions peuvent donner à l'optimiseur un indice supplémentaire. Les conditions de jointure ont une très faible sélectivité sur chaque table et une sélectivité élevée sur les tuples dans le produit croisé théorique. Les Conditions dans la déclaration où ont généralement une sélectivité beaucoup plus élevée.
dans une condition de jointure pour chaque rangée sur la gauche il y a très probablement une valeur sur la droite (et vice versa). Ainsi de telles conditions sont bonnes pour joindre des résultats de deux tables, mais ils doe pas beaucoup d'aide dans l'élimination des valeurs de chaque table individuelle de l'ensemble de résultats.
Conditions dans la clause où peut généralement être utilisé pour éliminer les lignes individuelles d'une table de l'ensemble de résultats.
indice pour le (Humain!) optimizer
est donc une bonne stratégie pour courir d'abord les conditions où sur l'individu tableaux et éliminer autant de lignes que possible de l'ensemble de résultats. Après cela on peut utiliser les conditions de jointure pour combiner les lignes survivantes.
il n'est pas clair si L'optimiseur Oracle utilise vraiment la position de la condition dans L'énoncé SQL comme indice pour optimiser la requête. Je suppose qu'il est plus intéressé par les faits durs dans les statistiques de tableau (il y a eu un certain changement dans la façon dont Oracle gère différents jointures dans 11g R1, voir ce post de L'équipe Oracle optimizer pour plus de détails).
au moins pour moi en tant qu'humain, il est très utile de savoir si un énoncé a une sélectivité sur une table simple quand j'essaie de comprendre et d'optimiser une requête. Vous devriez également considérer ceci, quand vous voulez mettre plusieurs conditions dans une clause ON (par exemple ON (a.x=b.x AND a.y=b.y ) vs. mettre une des conditions dans la classe où: il suffit de vérifier, comment la condition est sélective.
Conclusion
pour les requêtes existantes garder la syntaxe est. Lorsque vous créez une nouvelle requête ou refactor une requête existante, essayez de trier les prédicats sur la sélectivité en utilisant la syntaxe "JOIN ON": si elle n'est pas très sélectionnable sur une table simple mettez-la dans la partie ON, sinon dans la partie où.
le signe ( + ) utilisé dans les jointures externes est la syntaxe Oracle pour les jointures externes
D'après les informations recueillies auprès de sources oracle fiables, je pourrais voir que vous pouvez garder la syntaxe Oracle outer join(+) pour les paquets existants puisqu'il y a plus de 200 paquets et qu'à L'interne Oracle les convertit encore de la syntaxe ANSI à la syntaxe Oracle.
veuillez utiliser la syntaxe ANSI à l'avenir là où il y a des limitations pour l'utilisation de l'opérateur (+)
vous trouverez les liens pour plus d'explications sur le signe (+) les jointures Externes qui peuvent vous aider à décider sur votre migration
Oracle Outer Join syntaxe and ANSI syntaxe
Limitations de l'utilisation ( + ) des jointures extérieures
comme pratique, vous devez toujours utiliser la syntaxe ANSI. Mieux vaut ne pas retravailler sur les paquets et les procédures. Vous pouvez plutôt corriger les choses quand vous faites n'importe quelle maintenance sur ces scripts individuellement. Il n'y aura aucune différence dans des plans en raison de la syntaxe.
Quest SQL optimizer réécrit avec toutes les combinaisons possibles pour trouver un meilleur plan. Donc, vous devez toujours rechercher un SQL à partir de ses 100+ Résultats.
j'ai utilisé les deux approches sur des projets différents et je préfère la syntaxe JOIN .
- il y a une séparation claire pour les conditions d'assemblage dans
ONclause et filtre conditions dans la clauseWHERE. - il est plus facile de lire et de maintenir grand les requêtes avec beaucoup de jointures.
certains commentateurs ont dit que la syntaxe (+) ne permet pas les jointures externes complètes. Il le fait, donc ce n'est pas le problème: (gauche extérieure adhérer) UNION tous (droite extérieure adhérer).
d'Autres ont dit que la performance est la raison pour faire le changement. C'est un tas de conneries, surtout en SQL. Bien sûr, il y a quelques lignes directrices, mais chaque requête et chaque base de données a ses propres particularités, vous devez accorder pour spécifique pas pour des situations générales.
Les raisons de passer de (+), en plus de ne pas être standard, sont ses limites par opposition à la nouvelle syntaxe explicite: http://docs.oracle.com/cd/E16655_01/server.121/e17209/queries006.htm#SQLRF52354 . Commencez la lecture ici: "Oracle recommande que vous utilisiez la syntaxe de JOIN de clause externe plutôt que l'opérateur de join Oracle."
parler à partir de l'expérience dans le travail, en utilisant le JOIN plutôt que (+) est plus simple,plus rapide, mieux looking et mieux de travailler avec solution ,surtout lorsque vous travaillez avec multi base de données sélectionne (à travers les synonymes), avec beaucoup de tableaux en elle (ex.: 40 + tables) dans la grande base de données (1000+ tables,certaines tables avec plus de 2 milliards de lignes), vous sentirez une grande différence.