例如有多个union 会不会造成多次过滤select username from tab1
union
select username from tab2
union
select username from tab3
...
union
select username from tabnunion 是每次都去除重复行还是别的?select distinct username from (
select username from tab1
union all
select username from tab2
union all
select username from tab3
...
union all
select username from tabn
)好像只是过滤一次,效率会高吗
union
select username from tab2
union
select username from tab3
...
union
select username from tabnunion 是每次都去除重复行还是别的?select distinct username from (
select username from tab1
union all
select username from tab2
union all
select username from tab3
...
union all
select username from tabn
)好像只是过滤一次,效率会高吗
好些!
SQL> select tbrq from dbscgs_mx
2 union
3 select tbrq from dbscgs_mxbak
4 union
5 select trunc(lrrq) from dbscgs_mx
6 union
7 select trunc(lrrq) from dbscgs_mxbak;
......
已选择57行。实际:187select distinct rq from
(select tbrq rq from dbscgs_mx
union all
select tbrq rq from dbscgs_mxbak
union all
select trunc(lrrq) rq from dbscgs_mx
union all
select trunc(lrrq) rq from dbscgs_mxbak);
......已选择57行。实际:171
使用union时采用的SORT UNION COST=93,而
DISTINCT+UNION ALL采用的是 HASH UNION COST=806