带WHERE子句的UNION [英] UNION with WHERE clause

问题描述

我正在对Oracle数据库执行两个查询的UNION.他们两个都有一个WHERE子句.与在WHERE子句之后执行UNION相比，如果在执行UNION之后执行WHERE与在WHERE之后执行UNION相比，在性能上有区别吗?

I'm doing a UNION of two queries on an Oracle database. Both of them have a WHERE clause. Is there a difference in the performance if I do the WHERE after UNIONing the queries compared to performing the UNION after WHERE clause?

例如:

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

相比:

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

我相信在第二种情况下，它将对影响性能的两个表执行全表扫描.正确吗?

I believe in the second case, it performs a full table scan on both the tables affecting the performance. Is that correct?

推荐答案

以我的经验，Oracle非常擅长推销简单谓词.在Oracle 11.2上进行了以下测试.我敢肯定，它在所有10g版本上也会产生相同的执行计划.

In my experience, Oracle is very good at pushing simple predicates around. The following test was made on Oracle 11.2. I'm fairly certain it produces the same execution plan on all releases of 10g as well.

(请人们，如果您运行早期版本并尝试以下操作，请随时发表评论)

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)所看到的，谓词被下推并应用于排序(联合)之前.

As you can see at steps (4,5), the predicate is pushed down and applied before the sort (union).

我无法让优化器下推整个子查询，例如

I couldn't get the optimizer to push down an entire sub query such as

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

或加入.适当地设置PK/FK约束是可能的，但显然存在局限性:)

or a join. With proper PK/FK constraints in place it might be possible, but clearly there are limitations :)

这篇关于带WHERE子句的UNION的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！