Oracle UNION vs. UNION ALL: 位置，位置，位置

我想了解一下，Oracle如何确定行何时为重复行。虽然很清楚UNION和UNION ALL之间的有据可查的差异，但我想知道这种行为是否归因于规范，并且是关系代数的一部分。

我注意到以下两个语句的输出有所不同，具体取决于语句的顺序，或者更确切地说，将 “并集” 放置在何处:

select 1 from dual union --all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual;
复制

产生5行

select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union --all
select 1 from dual union all 
select 1 from dual;
复制

产生2行。

你能参考关系模型的理论并解释这种行为吗？

联合和联合都有同等的优先权。因此，两者都没有优先于另一个; 数据库从上到下处理它们。

所以第一句话:

联合1与1，返回一行
然后将剩余的四行合并，总共五行。

而第二个:

UNION ALLs行的第一个行，返回4行
将这些与下一行联合，返回1行
联盟进入了最后一排，总共给出了2个

您可以像其他操作一样使用括号来更改优先顺序。数据库首先处理括号内的所有内容。

所以这返回一行:

select 1 from dual union 
( select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual
);

1   
   1 
复制

这是因为括号强制数据库合并最后五行，然后将它们与结果中的第一行 => 一行合并。

查看执行计划还可以帮助您查看数据库在哪里应用union (all) 操作:

set serveroutput off

select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union 
select 1 from dual union all 
select 1 from dual;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC'));

---------------------------------                                           
| Id  | Operation        | Name |                                           
---------------------------------                                           
|   0 | SELECT STATEMENT |      |                                           
|   1 |  UNION-ALL       |      |                                           
|   2 |   SORT UNIQUE    |      |                                           
|   3 |    UNION-ALL     |      |                                           
|   4 |     FAST DUAL    |      |                                           
|   5 |     FAST DUAL    |      |                                           
|   6 |     FAST DUAL    |      |                                           
|   7 |     FAST DUAL    |      |                                           
|   8 |     FAST DUAL    |      |                                           
|   9 |   FAST DUAL      |      |                                           
---------------------------------

select 1 from dual union 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual;

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC'));

---------------------------------                                          
| Id  | Operation        | Name |                                          
---------------------------------                                          
|   0 | SELECT STATEMENT |      |                                          
|   1 |  UNION-ALL       |      |                                          
|   2 |   SORT UNIQUE    |      |                                          
|   3 |    UNION-ALL     |      |                                          
|   4 |     FAST DUAL    |      |                                          
|   5 |     FAST DUAL    |      |                                          
|   6 |   FAST DUAL      |      |                                          
|   7 |   FAST DUAL      |      |                                          
|   8 |   FAST DUAL      |      |                                          
|   9 |   FAST DUAL      |      |                                          
---------------------------------

select 1 from dual union 
( select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual union all 
select 1 from dual);

select * 
from   table(dbms_xplan.display_cursor(null, null, 'BASIC'));

---------------------------------                                           
| Id  | Operation        | Name |                                           
---------------------------------                                           
|   0 | SELECT STATEMENT |      |                                           
|   1 |  SORT UNIQUE     |      |                                           
|   2 |   UNION-ALL      |      |                                           
|   3 |    FAST DUAL     |      |                                           
|   4 |    FAST DUAL     |      |                                           
|   5 |    FAST DUAL     |      |                                           
|   6 |    FAST DUAL     |      |                                           
|   7 |    FAST DUAL     |      |                                           
|   8 |    FAST DUAL     |      |                                           
--------------------------------- 
复制