Reglas generales para simplificar instrucciones SQL

Aquí hay algunos de trabajar con Oracle 8 & 9 (por supuesto, a veces hacer lo contrario podría hacer que la consulta sea más simple o más rápida):

Los paréntesis se pueden eliminar si no se utilizan para anular la precedencia del operador. Un ejemplo simple es cuando todos los operadores booleanos en su cláusula where son los mismos: where ((a or b) or c) es equivalente a where a or b or c.

Una sub-consulta puede a menudo (si no siempre) ser fusionada con la consulta principal para simplificarla. En mi experiencia, esto a menudo mejora el rendimiento considerablemente:

select foo.a,
       bar.a
  from foomatic  foo,
       bartastic bar
 where foo.id = bar.id and
       bar.id = (
         select ban.id
           from bantabulous ban
          where ban.bandana = 42
       )
;

Es equivalente a

select foo.a,
       bar.a
  from foomatic    foo,
       bartastic   bar,
       bantabulous ban
 where foo.id = bar.id and
       bar.id = ban.id and
       ban.bandana = 42
;

Usando ANSI joins separa una gran cantidad de lógica "code monkey" de las partes realmente interesantes de la cláusula where: La consulta anterior es equivalente a

select foo.a,
       bar.a
  from foomatic    foo
  join bartastic   bar on bar.id = foo.id
  join bantabulous ban on ban.id = bar.id
 where ban.bandana = 42
;

Si desea verificar la existencia de una fila, no use count(*), en su lugar use rownum = 1 o coloque la consulta en una cláusula where exists para obtener solo una fila en lugar de todas.

• Supongo que la obvia es buscar cualquier Cursor que pueda ser reemplazado con una operación basada en SQL 'Set'.
• Lo siguiente en mi lista es buscar cualquier sub-consulta correlacionada que pueda ser reescrita como una consulta no correlacionada
• En procedimientos almacenados largos, divida instrucciones SQL separadas en sus propios procedimientos almacenados. De esa manera, obtendrán su propio plan de consultas en caché.
• Busque transacciones que puedan tener su alcance acortado. Regularmente encuentro declaraciones dentro de un transacción que puede estar fuera de forma segura.
• Las subeleccionas a menudo se pueden reescribir como uniones directas (los optimizadores modernos son buenos para detectar las simples)

Como @Quassnoi mencionó, el Optimizador a menudo hace un buen trabajo. Una forma de ayudarlo es asegurarse de que los índices y las estadísticas estén actualizados y que existan índices adecuados para su carga de trabajo de consulta.

Me gusta reemplazar todo tipo de subselección por consulta de unión.

Este es obvio:

SELECT  *
FROM    mytable mo
WHERE   EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

Por

SELECT  mo.*
FROM    mytable mo inner join othertable o on o.othercol = mo.col

Y este está bajo estimación:

SELECT  *
FROM    mytable mo
WHERE   NOT EXISTS
        (
          SELECT  *
          FROM    othertable o
          WHERE   o.othercol = mo.col
        )

Por

SELECT  mo.*
FROM    mytable mo left outer join othertable o on o.othercol = mo.col
WHERE   o.othercol is null

Podría ayudar al DBMS a elegir el buen plan de ejecución en una solicitud grande.

Me gusta que todos en un equipo sigan un conjunto de estándares para hacer que el código sea legible, mantenible, comprensible, lavable, etc.. :)

• todos usan el mismo alias
• no hay cursores. sin bucles
• ¿por qué incluso pensar EN cuando se puede EXISTE
• GUIÓN
• Coherencia en el estilo de codificación

Hay algunas cosas más aquí ¿Cuáles son algunos de sus estándares de base de datos más útiles?

Dada la naturaleza de SQL, es absolutamente necesario ser consciente de las implicaciones de rendimiento de cualquier refactorización. Refactorización SQL Applications es un buen recurso en refactorización con un fuerte énfasis en el rendimiento (ver Capítulo 5).

Aunque la simplificación puede no ser igual a la optimización, la simplificación puede ser importante en la escritura de código SQL legible, que a su vez es fundamental para poder comprobar su código SQL para la corrección conceptual (no la corrección sintáctica, que su entorno de desarrollo debe comprobar por usted). Me parece que en un mundo ideal, escribiríamos el código SQL más simple y legible y luego el optimizador reescribiría ese código SQL para que estuviera en cualquier forma (quizás más detallada) ejecutaría el el más rápido.

He encontrado que pensar en las sentencias SQL como basadas en la lógica de conjunto es muy útil, particularmente si necesito combinar cláusulas where o averiguar una negación compleja de una cláusula where. Utilizo las leyes del álgebra booleana en este caso.

Las más importantes para simplificar una cláusula where son probablemente las Leyes de DeMorgan (tenga en cuenta que "·" es "Y" y "+" es "O"):

• NO (x * y) = NO x + NO y
• NO (x + y) = NO x * NO y

Esto se traduce en SQL a:

NOT (expr1 AND expr2) -> NOT expr1 OR NOT expr2
NOT (expr1 OR expr2) -> NOT expr1 AND NOT expr2

Estas leyes pueden ser muy útiles para simplificar las cláusulas where con muchas partes anidadas AND y OR.

También es útil recordar que la declaración field1 IN (value1, value2, ...) es equivalente a field1 = value1 OR field1 = value2 OR ... . Esto le permite negar el IN () una de dos maneras:

NOT field1 IN (value1, value2)  -- for longer lists
NOT field1 = value1 AND NOT field1 = value2  -- for shorter lists

Una sub-consulta puede ser pensada de esta manera también. Por ejemplo, esto negaba la cláusula where:

NOT (table1.field1 = value1 AND EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

Se puede reescribir como:

NOT table1.field1 = value1 OR NOT EXISTS (SELECT * FROM table2 WHERE table1.field1 = table2.field2))

Estas leyes hacen no le dice cómo transformar una consulta SQL usando una subconsulta en una usando una combinación, pero la lógica booleana puede ayudarlo a comprender los tipos de combinación y lo que debe devolver su consulta. Por ejemplo, con mesas A y B un INNER JOIN es como A AND B, a LEFT OUTER JOIN es como (A AND NOT B) OR (A AND B) que se simplifica a A OR (A AND B), y FULL OUTER JOIN es A OR (A AND B) OR B que se simplifica a A OR B.

Mi enfoque es aprender teoría relacional en general y álgebra relacional en particular. Luego aprenda a detectar las construcciones utilizadas en SQL para implementar operadores del álgebra relacional (por ejemplo, cuantificación universal, también conocida como división) y el cálculo (por ejemplo, cuantificación existencial). El truco es que SQL tiene características que no se encuentran en el modelo relacional, por ejemplo, nulls, que probablemente sea mejor refactorizar de todos modos. Lectura recomendada: SQL y Teoría Relacional: Cómo Escribir con Precisión Código SQL Por C. J. Date .

En este sentido, no estoy convencido de que "el hecho de que la mayoría de las subselecciones se puedan reescribir como una COMBINACIÓN" represente una simplificación.

Tome esta consulta por ejemplo:

SELECT c 
  FROM T1 
 WHERE c NOT IN ( SELECT c FROM T2 );

Reescribir usando JOIN

SELECT DISTINCT T1.c 
  FROM T1 NATURAL LEFT OUTER JOIN T2 
 WHERE T2.c IS NULL;

La combinación es más detallada!

Alternativamente, reconozca que el constructo está implementando una antijoin en la proyección de c por ejemplo, pseudo algrbra

T1 { c } antijoin T2 { c }

Simplificación usando operadores relacionales:

SELECT c FROM T1 EXCEPT SELECT c FROM T2;