subqueries - oracle sql join syntax
Oracle Joins-Comparación entre la sintaxis convencional VS ANTA Syntax (10)
Separación de predicados basados en la selectividad
Separación de su consulta en condiciones de unión y donde las condiciones pueden darle al optimizador una pista adicional. Las condiciones de unión tienen una selectividad muy baja en cada tabla y una alta selectividad en las tuplas en el producto cruzado teórico. Las condiciones en la declaración where generalmente tienen una selectividad mucho más alta.
En una condición de unión para cada fila a la izquierda, es muy probable que haya un valor a la derecha (y viceversa). Entonces estas condiciones son buenas para unir los resultados de dos tablas, pero no ayudan mucho a eliminar los valores de cada tabla individual del conjunto de resultados.
Las condiciones en la cláusula where generalmente se pueden usar para eliminar filas individuales de una tabla del conjunto de resultados.
Sugerencia para el optimizador (humano)
Entonces, es una buena estrategia ejecutar primero las condiciones en las tablas individuales y eliminar tantas filas como sea posible del conjunto de resultados. Después de eso, uno puede usar las condiciones de combinación para combinar las filas supervivientes.
No está claro si el optimizador de Oracle realmente usa la posición de la condición en la declaración SQL como una sugerencia para optimizar la consulta. Supongo que está más interesado en los hechos reales en las estadísticas de la tabla (hubo algún cambio en la forma en que Oracle maneja diferentes combinaciones en 11g R1; consulte esta publicación del equipo optimizador de Oracle para obtener más detalles).
Al menos para mí, como ser humano, es muy útil saber si una declaración tiene selectividad en una tabla singe cuando trato de entender y optimizar una consulta. También debería considerar esto, cuando quiera poner condiciones múltiples en una cláusula ON (por ejemplo, ON (ax=bx AND ay=by ) frente a poner una de las condiciones en el orden de salida: simplemente compruebe qué tan selectiva es la condición.
Conclusión
Para las consultas existentes, mantenga la sintaxis tal como está. Al crear una nueva consulta o refactorizar una existente, intente ordenar los predicados en selectividad usando la sintaxis "JOIN ON": si no es muy seleccionable en una sola tabla, colóquela en la parte ON, de lo contrario, en la parte WHERE.
Preámbulo
Últimamente, veo demasiados geeks comentando sobre las preguntas de Oracle diciendo que "No use el operador (+), más bien use la sintaxis JOIN".
Pregunta
Veo que ambos funcionan bien. Pero, ¿cuál es la verdadera diferencia entre usarlos y qué te hace sentir usarlos? Me gustaría recibir respuestas, más por experiencia.
1. Is there anything to do with limitations in application, performance,
etc. while using them?
2. What would you suggest for me?
Leí algo en la documentación de Oracle, pero no lo suficiente como para hacerme entender o sentirme cómodo con la información completa.
Nota : planeo migrar más de 200 paquetes y procedimientos, si la palabra clave debe usarse en lugar de (+)
3. Also is there any freeware tools to do the rewrite?
Publicando muestras
┌───────────────────────────────────┬─────────────────────────────────────────────┐
│ 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; │ │
└───────────────────────────────────┴─────────────────────────────────────────────┘
PD : lea el resumen de respuestas para todas las actualizaciones agrupadas.
Además de los motivos mencionados por otros, el uso de los operadores JOIN garantiza que su código SQL sea compatible con ANSI y, por lo tanto, permitiría que una aplicación de front-end se transfiera más fácilmente a otras plataformas de bases de datos.
Algunos comentaristas han dicho que la sintaxis (+) no permite combinaciones externas completas. Sí, así que este no es el problema: (UNIÓN EXTREMA DE LA IZQUIERDA) UNIÓN TODO (UNIÓN EXTERIOR DERECHA).
Otros han dicho que el rendimiento es la razón para hacer el cambio. Es un montón de BS, especialmente en SQL. Por supuesto, hay algunas pautas, pero cada consulta y cada base de datos tiene sus propias peculiaridades, debe sintonizar situaciones específicas y no generales.
Las razones para cambiar de (+), además de no ser estándar, son sus limitaciones en comparación con la nueva sintaxis explícita: http://docs.oracle.com/cd/E16655_01/server.121/e17209/queries006.htm#SQLRF52354 . Comience leyendo desde aquí: "Oracle recomienda que use la sintaxis OUTER JOIN de la cláusula FROM en lugar del operador de Oracle join".
Como práctica, siempre debes usar la sintaxis ANSI. Mejor no volver a trabajar en paquetes y procedimientos. Más bien, puede arreglar las cosas cuando realiza cualquier mantenimiento en esos scripts de forma individual. No habrá ninguna diferencia en los planes debido a la sintaxis.
Quest SQL optimizer reescribe con todas las combinaciones posibles para encontrar un mejor plan. Por lo tanto, aún necesita buscar un SQL entre sus más de 100 resultados.
El signo (+) usado en uniones externas es la sintaxis de Oracle para uniones externas
A partir de la información recopilada a partir de fuentes confiables de Oracle pude ver que puede mantener la sintaxis de combinación externa de Oracle (+) para los paquetes existentes, ya que hay más de 200 paquetes y, internamente, Oracle todavía los convierte de la sintaxis ANSI a la sintaxis de Oracle.
Utilice amablemente la sintaxis ANSI en el futuro donde haya limitaciones para usar el operador (+)
Encuentre los enlaces para obtener una explicación detallada sobre el signo (+) Uniones externas que pueden ayudarlo a decidir sobre su migración
Sintaxis de unión externa de Oracle y sintaxis ANSI
Limitaciones de uso (+) Uniones externas
Más información sobre la combinación externa de Oracle
(+) Outer outer Operator recomendado en Java cuando se utilizan controladores Oracle JDBC
En 11g, debería usar la sintaxis de unión ANSI. Es más flexible (soporte para combinaciones externas completas y uniones particionadas), y como dice la documentación:
Oracle recomienda encarecidamente que utilice la sintaxis de combinación de la cláusula FROM más flexible que se muestra en el ejemplo anterior.
Esa es una razón suficiente.
Hablando de la experiencia en el trabajo, usar JOIN lugar de (+) es más simple, más rápido, más atractivo y mejor para trabajar con solución, especialmente cuando trabajas con selecciones de bases de datos múltiples (a través de sinónimos), con muchas tablas ( ej .: más de 40 tablas) en una base de datos grande (más de 1000 tablas, algunas tablas con más de 2 mil millones de filas), notará una gran diferencia.
Si sus más de 200 paquetes funcionan según lo previsto con la sintaxis "pasada de moda", déjelo. SQL no comenzará a funcionar mejor después de la migración a la sintaxis ANSI; es simplemente una sintaxis diferente.
Dicho todo esto, la sintaxis de ANSI es más clara: no se va a unir normalmente si olvida (+) en alguna combinación externa de varias columnas.
En el pasado, existían algunos errores con sintaxis ANSI, pero si vas con los últimos 11.2 o 12.1 que ya deberían estar corregidos.
Por supuesto, conoce mejor su entorno y sus prioridades, como dijo SchmitzIT, la sintaxis ANSI es parte del estándar SQL y ayudaría al usar algún otro producto RDBMS.
Usé ambos enfoques en diferentes proyectos y prefiero la sintaxis JOIN .
- Existe una separación clara para las condiciones de unión en la cláusula
ONy las condiciones de filtro en la cláusulaWHERE. - Es más fácil leer y mantener grandes consultas con muchas combinaciones.
Agrupando respuestas juntas
- Usar uniones explícitas en lugar de implícitas (independientemente de si son uniones externas o no) es que es mucho más fácil crear accidentalmente un producto cartesiano con las combinaciones implícitas. Con los JOINs explícitos no puedes crear "uno por accidente". Mientras más mesas estén involucradas, mayor es el riesgo de que pierda una condición de unión.
- Básicamente (+) es muy limitado en comparación con las uniones ANSI. Además, solo está disponible en Oracle, mientras que la sintaxis de unión ANSI es compatible con todos los principales DBMS.
- SQL no comenzará a funcionar mejor después de la migración a la sintaxis ANSI; es simplemente una sintaxis diferente.
- Oracle recomienda encarecidamente que utilice la sintaxis de combinación de la cláusula FROM más flexible que se muestra en el ejemplo anterior. En el pasado, existían algunos errores con sintaxis ANSI, pero si vas con los últimos 11.2 o 12.1 que ya deberían estar corregidos.
- El uso de los operadores JOIN garantiza que su código SQL sea compatible con ANSI y, por lo tanto, permitiría que una aplicación de front-end se transfiera más fácilmente a otras plataformas de bases de datos.
- Las condiciones de unión tienen una selectividad muy baja en cada tabla y una alta selectividad en las tuplas en el producto cruzado teórico. Las condiciones en la declaración where generalmente tienen una selectividad mucho más alta.
- Oracle convierte internamente la sintaxis ANSI a la sintaxis (+), puede ver que esto sucede en la sección Información del predicado del plan de ejecución.
Posible error al utilizar la sintaxis ANSI en el motor 12c
Incluyendo una posibilidad de error en JOIN en 12c. Mira here
SEGUIR:
Quest SQL optimizer tool reescribe la sintaxis de SQL a ANSI.