query ejemplos datos consultas condiciones con clausulas clausula avanzadas sql oracle where

ejemplos - Orden de ejecución de condiciones en la cláusula SQL ''where''



query en sql (6)

Tengo un conjunto de condiciones en mi cláusula where

WHERE d.attribute3 = ''abcd*'' AND x.STATUS != ''P'' AND x.STATUS != ''J'' AND x.STATUS != ''X'' AND x.STATUS != ''S'' AND x.STATUS != ''D'' AND CURRENT_TIMESTAMP - 1 < x.CREATION_TIMESTAMP

¿Cuál de estas condiciones se ejecutará primero? Estoy usando Oracle.

¿Recibiré estos detalles en mi plan de ejecución? (No tengo la autoridad para hacer eso en el DB aquí, de lo contrario lo hubiera intentado)

¿Estás seguro de que "no tienes la autoridad" para ver un plan de ejecución? ¿Qué pasa con el uso de AUTOTRACE?

SQL> set autotrace on SQL> select * from emp 2 join dept on dept.deptno = emp.deptno 3 where emp.ename like ''K%'' 4 and dept.loc like ''l%'' 5 / no rows selected Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 62 | 4 (0)| | 1 | NESTED LOOPS | | 1 | 62 | 4 (0)| |* 2 | TABLE ACCESS FULL | EMP | 1 | 42 | 3 (0)| |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| |* 4 | INDEX UNIQUE SCAN | SYS_C0042912 | 1 | | 0 (0)| ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMP"."ENAME" LIKE ''K%'' AND "EMP"."DEPTNO" IS NOT NULL) 3 - filter("DEPT"."LOC" LIKE ''l%'') 4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

Como puede ver, eso proporciona muchos detalles sobre cómo se ejecutará la consulta. Me dice que:

  • la condición "emp.ename like ''K%''" se aplicará primero, en el análisis completo de EMP
  • luego, los registros DEPT coincidentes se seleccionarán a través del índice en dept.deptno (a través del método NESTED LOOPS)
  • finalmente se aplicará el filtro "dept.loc like ''l%''.

Este orden de aplicación no tiene nada que ver con la forma en que se ordenan los predicados en la cláusula WHERE, como podemos mostrar con esta consulta reordenada:

SQL> select * from emp 2 join dept on dept.deptno = emp.deptno 3 where dept.loc like ''l%'' 4 and emp.ename like ''K%''; no rows selected Execution Plan ---------------------------------------------------------- ---------------------------------------------------------------------------------- | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| ---------------------------------------------------------------------------------- | 0 | SELECT STATEMENT | | 1 | 62 | 4 (0)| | 1 | NESTED LOOPS | | 1 | 62 | 4 (0)| |* 2 | TABLE ACCESS FULL | EMP | 1 | 42 | 3 (0)| |* 3 | TABLE ACCESS BY INDEX ROWID| DEPT | 1 | 20 | 1 (0)| |* 4 | INDEX UNIQUE SCAN | SYS_C0042912 | 1 | | 0 (0)| ---------------------------------------------------------------------------------- Predicate Information (identified by operation id): --------------------------------------------------- 2 - filter("EMP"."ENAME" LIKE ''K%'' AND "EMP"."DEPTNO" IS NOT NULL) 3 - filter("DEPT"."LOC" LIKE ''l%'') 4 - access("DEPT"."DEPTNO"="EMP"."DEPTNO")

Como se ha dicho, mirar el plan de ejecución le dará cierta información. Sin embargo, a menos que utilice la función de estabilidad del plan, no puede confiar en que el plan de ejecución siempre permanezca igual.

En el caso de la consulta que publicó, no parece que el orden de evaluación cambie la lógica de ninguna manera, así que supongo que lo que está pensando es eficiencia. Es bastante probable que el optimizador de Oracle elija un plan que sea eficiente.

Hay trucos que puede hacer para fomentar un pedido en particular si desea comparar el rendimiento con la consulta base. Digamos, por ejemplo, que quería que se ejecutara primero la condición de marca de tiempo. Podrías hacer esto:

WITH subset AS ( SELECT /*+ materialize */ FROM my_table WHERE CURRENT_TIMESTAMP - 1 < x.CREATION_TIMESTAMP ) SELECT * FROM subset WHERE d.attribute3 = ''abcd*'' AND x.STATUS != ''P'' AND x.STATUS != ''J'' AND x.STATUS != ''X'' AND x.STATUS != ''S'' AND x.STATUS != ''D''

La sugerencia de "materialización" debe hacer que el optimizador ejecute primero la consulta en línea, luego escanee ese conjunto de resultados para las otras condiciones.

No estoy aconsejando que hagas esto como un hábito general. En la mayoría de los casos, solo escribir la consulta simple conducirá a los mejores planes de ejecución.

La base de datos decidirá en qué orden ejecutar las condiciones.

Normalmente (pero no siempre) usará un índice primero donde sea posible.

Para agregar a los otros comentarios en los planes de ejecución, bajo el modelo de costo basado en cpu introducido en 9i y utilizado por defecto en 10g + Oracle también hará una evaluación de qué orden de evaluación de predicado dará como resultado un costo computacional menor, incluso si eso no afecta el orden de acceso a la tabla y método. Si ejecutar un predicado antes que otro da como resultado menos cálculos de predicados que se ejecutan, entonces se puede aplicar esa optimización.

Vea este artículo para más detalles: http://www.oracle.com/technology/pub/articles/lewis_cbo.html

Además, Oracle ni siquiera tiene que ejecutar predicados donde la comparación con una restricción de comprobación o definiciones de partición indique que no se devolverán filas de todos modos.

Cosas complejas

Finalmente, la teoría de base de datos relacional dice que nunca se puede depender del orden de ejecución de las cláusulas de consulta, así que mejor no intentarlo. Como han dicho otros, el optimizador basado en costos intenta elegir lo que cree que es mejor, pero incluso ver el plan de explicación no garantizará el orden real que se usa. El plan Explicar simplemente te dice lo que recomienda la CBO, pero todavía no es 100%.

Tal vez si explicas por qué estás tratando de hacer esto, ¿algunos podrían sugerir un plan?

Pregunta capciosa. Simplemente enfrentaba el mismo dilema. Necesito mencionar una función dentro de una consulta. La función en sí misma realiza otra consulta, por lo que comprende cómo afecta el rendimiento en general. Pero en la mayoría de los casos que tenemos, la función no se llamaría tan a menudo si el resto de las condiciones se ejecutaran primero.

Bueno, pensé que sería útil publicar aquí otro artículo para el tema.

La siguiente cita se copia del sitio de Donald Burleson ( http://www.dba-oracle.com/t_where_clause.htm ).

La sugerencia ordered_predicates se especifica en la cláusula WHERE de Oracle de una consulta y se utiliza para especificar el orden en que se evaluarán los predicados booleanos.

En ausencia de ordered_predicates , Oracle usa los siguientes pasos para evaluar el orden de los predicados de SQL:

  • Las subconsultas se evalúan antes de las condiciones booleanas externas en la cláusula WHERE.

  • Todas las condiciones booleanas sin funciones incorporadas o subconsultas se evalúan en orden inverso al orden en que se encuentran en la cláusula WHERE, con el último predicado que se evalúa primero.

  • Los predicados booleanos con funciones integradas de cada predicado se evalúan en orden creciente de sus costos de evaluación estimados.