СОЮЗ с предложением WHERE

Ответ 1

По моему опыту, 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 это может быть возможно, но, очевидно, существуют ограничения:)

Ответ 2

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

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

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

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

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

Ответ 3

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

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

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, что делает его совсем другим запросом. Если столбцы сглажены таким образом, это может запутать.

Ответ 4

Вам нужно взглянуть на планы объяснений, но если на COL_A нет INDEX или PARTITION, вы смотрите на FULL TABLE SCAN на обе таблицы.

С учетом этого в первом примере вы выбрасываете некоторые данные, так как он выполняет FULL TABLE SCAN. Этот результат сортируется UNION, затем дублируются данные. Это дает вам набор результатов.

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

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

Ответ 5

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

Ответ 6

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

Ответ 7

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

Ответ 8

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

Ответ 9

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