UNION с предложением WHERE

По моему опыту, Oracle очень хорошо продвигает простые предикаты. Следующий тест был сделан на Oracle 11.2. Я совершенно уверен, что он создает один и тот же план выполнения для всех выпусков 10g.

(Пожалуйста, не стесняйтесь оставлять комментарии, если вы запускали более раннюю версию и пробовали следующее)

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)

Как вы можете видеть на шагах (4,5), предикат опускается и применяется перед сортировкой (объединением).

Я не мог заставить оптимизатор вытолкнуть весь подзапрос, такой как

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

или присоединение. При наличии правильных ограничений PK/FK это может быть возможно, но очевидно, что ограничения есть :)

Просто предостережение

Если вы попытались

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

в сравнении с:

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

Затем во втором запросе colA в предложении where на самом деле будет иметь colX из tableB, что делает его совершенно другим запросом. Если столбцы имеют псевдонимы таким образом, это может привести к путанице.

ПРИМЕЧАНИЕ. Несмотря на то, что мой совет был верным много лет назад, оптимизатор Oracle улучшился, так что расположение здесь больше не имеет значения. Однако предпочтение UNION ALL вместо UNION всегда будет правильным, и переносимый SQL должен избегать зависимости от оптимизаций, которые могут быть не во всех базах данных.

Короткий ответ: вам нужно WHERE перед UNION, и вы хотите использовать UNION ALL, если это вообще возможно. Если вы используете UNION ALL, проверьте вывод EXPLAIN, Oracle может быть достаточно умным, чтобы оптимизировать условие WHERE, если оно осталось после него.

Причина в следующем. В определении UNION говорится, что если в двух наборах данных есть дубликаты, их необходимо удалить. Поэтому в этой операции есть неявный GROUP BY, который имеет тенденцию быть медленным. Что еще хуже, оптимизатор Oracle (по крайней мере, 3 года назад, и я не думаю, что он изменился) не пытается проталкивать условия через GROUP BY (неявные или явные). Поэтому Oracle приходится строить большие наборы данных, чем необходимо, группировать их и только потом приступать к фильтрации. Таким образом, предварительная фильтрация везде, где это возможно, официально является хорошей идеей. (Вот почему, кстати, важно по возможности помещать условия в WHERE, а не оставлять их в предложении HAVING.)

Кроме того, если вы знаете, что между двумя наборами данных не будет дубликатов, используйте UNION ALL. Это похоже на UNION тем, что он объединяет наборы данных, но не пытается дедуплицировать данные. Это экономит дорогостоящую операцию группировки. По моему опыту, довольно часто можно воспользоваться этой операцией.

Поскольку в UNION ALL нет неявного GROUP BY, возможно, оптимизатор Oracle знает, как протолкнуть через него условия. У меня нет Oracle для тестирования, поэтому вам нужно будет проверить это самостоятельно.

Вам нужно посмотреть на планы объяснения, но если в COL_A нет ИНДЕКСА или РАЗДЕЛА, вы просматриваете ПОЛНОЕ СКАНИРОВАНИЕ ТАБЛИЦЫ в обеих таблицах.

Имея это в виду, ваш первый пример выбрасывает некоторые данные, поскольку он выполняет ПОЛНОЕ СКАНИРОВАНИЕ ТАБЛИЦЫ. Этот результат сортируется с помощью UNION, а затем повторяющиеся данные удаляются. Это дает вам набор результатов.

Во втором примере вы извлекаете полное содержимое обеих таблиц. Этот результат, вероятно, будет больше. Таким образом, UNION сортирует больше данных, а затем удаляет дубликаты. Затем фильтр применяется, чтобы дать вам набор результатов, который вам нужен.

Как правило, чем раньше вы отфильтруете данные, тем меньше будет набор данных и тем быстрее вы получите результаты. Как всегда, ваш пробег может отличаться.

Я бы удостоверился, что у вас есть индекс для ColA, а затем запустил их оба и замерил время. Это даст вам лучший ответ.

я думаю, это будет зависеть от многих вещей - запустите EXPLAIN PLAN для каждого, чтобы увидеть, что выберет ваш оптимизатор. В противном случае, как предлагает @rayman, запустите их оба и засеките время.

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

Если мы используем объединение, которое содержит одно и то же имя поля в 2 таблицах, нам нужно указать имя для подзапроса как tableC (в приведенном выше запросе). Наконец, условие WHERE должно быть WHERE tableC.colA > 1