问题描述
我正在对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 UNION
ing 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的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!