sql - from - UNIÓN con la cláusula WHERE

union mysql queries

Question

Estoy haciendo una UNION de dos consultas en una base de datos Oracle. Ambos tienen una cláusula WHERE . ¿Hay alguna diferencia en el rendimiento si hago el WHERE después de la UNION de las consultas en comparación con la realización de la UNION después de la cláusula WHERE ?

Por ejemplo:

SELECT colA, colB FROM tableA WHERE colA > 1
UNION
SELECT colA, colB FROM tableB WHERE colA > 1

comparado con:

SELECT * 
  FROM (SELECT colA, colB FROM tableA
        UNION
        SELECT colA, colB FROM tableB) 
 WHERE colA > 1

Creo que en el segundo caso, realiza una exploración de tabla completa en ambas tablas que afectan el rendimiento. ¿Es eso correcto?

Answer 1

En mi experiencia, Oracle es muy bueno para impulsar predicados simples . La siguiente prueba se realizó en Oracle 11.2. Estoy bastante seguro de que produce el mismo plan de ejecución en todas las versiones de 10g.

(Por favor, personas, pueden dejar un comentario si ejecuta una versión anterior y prueba lo siguiente)

create table table1(a number, b number);
create table table2(a number, b number);

explain plan for
select *
  from (select a,b from table1
        union 
        select a,b from table2
       )
 where a > 1;

select * 
  from table(dbms_xplan.display(format=>''basic +predicate''));

PLAN_TABLE_OUTPUT
---------------------------------------
| Id  | Operation            | Name   |
---------------------------------------
|   0 | SELECT STATEMENT     |        |
|   1 |  VIEW                |        |
|   2 |   SORT UNIQUE        |        |
|   3 |    UNION-ALL         |        |
|*  4 |     TABLE ACCESS FULL| TABLE1 |
|*  5 |     TABLE ACCESS FULL| TABLE2 |
---------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------    
   4 - filter("A">1)
   5 - filter("A">1)

Como puede ver en los pasos (4,5), el predicado se empuja hacia abajo y se aplica antes del género (unión).

No pude hacer que el optimizador empujara hacia abajo una consulta secundaria completa, como

 where a = (select max(a) from empty_table)

o unirme. Con las restricciones PK / FK correctas, podría ser posible, pero claramente existen limitaciones :)

Answer 2

Me aseguraría de tener un índice de ColA, y luego ejecutarlos y cronometrarlos. Eso te daría la mejor respuesta.

Answer 3

Necesita ver los planes de explicación, pero a menos que haya un ÍNDICE o PARTICIÓN en COL_A, está viendo una EXPLORACIÓN DE LA TABLA COMPLETA en ambas tablas.

Con esto en mente, su primer ejemplo es arrojar algunos de los datos como lo hace el ESCANEO DE LA MESA COMPLETA. Ese resultado está siendo ordenado por la UNIÓN, luego se eliminan los datos duplicados. Esto te da tu conjunto de resultados.

En el segundo ejemplo, está extrayendo el contenido completo de ambas tablas. Ese resultado es probable que sea más grande. Entonces la UNIÓN está ordenando más datos, y luego eliminando los duplicados. Luego, el filtro se aplica para darle el conjunto de resultados que busca.

Como regla general, cuanto antes filtre los datos, menor será el conjunto de datos y más rápido obtendrá los resultados. Como siempre, su kilometraje puede variar.

Answer 4

Solo una advertencia

Si lo intentaste

SELECT colA, colB FROM tableA WHERE colA > 1
UNION
SELECT colX, colA FROM tableB WHERE colA > 1

comparado con:

SELECT * 
  FROM (SELECT colA, colB FROM tableA
        UNION
        SELECT colX, colA FROM tableB) 
 WHERE colA > 1

Luego, en la segunda consulta, la colA en la cláusula where tendrá en realidad el colX de la tabla B, lo que la convierte en una consulta muy diferente. Si las columnas se alias de esta manera, puede ser confuso.

Answer 5

creo que dependerá de muchas cosas: ejecute EXPLAIN PLAN en cada una de ellas para ver lo que selecciona su optimizador. De lo contrario, como sugiere @rayman, ejecútelos y cronomenelos.

Answer 6

NOTA: Si bien mi consejo fue cierto hace muchos años, el optimizador de Oracle ha mejorado, por lo que la ubicación del lugar definitivamente ya no importa aquí. Sin embargo, preferir UNION ALL vs UNION siempre será cierto, y SQL portátil debería evitar depender de optimizaciones que pueden no estar en todas las bases de datos.

Respuesta corta, quiere el WHERE antes de la UNION y desea usar UNION ALL si es posible. Si está utilizando UNION ALL luego verifica la salida EXPLAIN, Oracle podría ser lo suficientemente inteligente como para optimizar la condición WHERE si se deja después.

La razón es la siguiente. La definición de UNION dice que si hay duplicados en los dos conjuntos de datos, deben ser eliminados. Por lo tanto, hay un GROUP BY implícito en esa operación, que tiende a ser lento. Peor aún, el optimizador de Oracle (al menos desde hace 3 años, y no creo que haya cambiado) no intenta impulsar las condiciones a través de un GROUP BY (implícito o explícito). Por lo tanto, Oracle tiene que construir conjuntos de datos más grandes que los necesarios, agruparlos y solo luego filtrarlos. Por lo tanto, el prefiltrado siempre que sea posible es oficialmente una buena idea. (Esto es, por cierto, por qué es importante poner condiciones en el WHERE siempre que sea posible en lugar de dejarlas en una cláusula HAVING ).

Además, si sabe que no habrá duplicados entre los dos conjuntos de datos, utilice UNION ALL . Eso es como UNION en que concatena conjuntos de datos, pero no trata de deduplicar datos. Esto ahorra una costosa operación de agrupación. En mi experiencia, es bastante común poder aprovechar esta operación.

Como UNION ALL no tiene un GROUP BY implícito en él, es posible que el optimizador de Oracle sepa cómo impulsar las condiciones a través de él. No tengo a Oracle sentado para probar, por lo que tendrá que probarlo usted mismo.

Answer 7

SELECT * 
FROM (SELECT * FROM can
    UNION
    SELECT * FROM employee) as e
WHERE e.id = 1;

Answer 8

SELECT * FROM (SELECT colA, colB FROM tableA UNION SELECT colA, colB FROM tableB) as tableC WHERE tableC.colA > 1

Si estamos usando una unión que contiene el mismo nombre de campo en 2 tablas, entonces necesitamos dar un nombre a la consulta secundaria como tabla C (en la consulta anterior). Finalmente, la condición WHERE debería ser WHERE tableC.colA > 1

Answer 9

SELECT colA, colB FROM tableA  WHERE colA > 1
UNION
SELECT colX, colA FROM tableB