Oracle union and union all efficiency and Difference Analysis

Union performs union operations on two result sets, excluding duplicate rows and sorting by default rules;

Union all performs union operations on two result sets, including duplicate rows without sorting.

Union all is much faster than union, so if you can confirm that the two results of the merge do not contain duplicate data, use union all, as shown below:

Try to use union all because union needs to be sorted to remove duplicate records, which is less efficient.
Union
If the table has multiple index columns, replacing the or in the where clause with union will improve the efficiency. If the index column is used or, full table scan is performed. If column does not use an index, remember or.

Select date from store_information
Union
Select date from internet_sales

Note: In union usage, the Field Types of the two select statements must match and the number of fields must be the same. In the preceding example, more complex conditions may occur during the actual software development process, for more information, see the following example.

Select '1' as type, fl_id, fl_code, fl_cname, flda. fl_parentid from flda
Where zt_id = 1, 2006030002
Union
Select '2' as type, xm_id, xm_code, xm_cname, fl_id from xmda
Where exists (select * from (select fl_id from flda where zt_id = 2006030002) a where xmda. fl_id = a. fl_id)
Order by type, fl_parentid, fl_id

The query condition is to check whether the fl_id in the xmda table matches the fl_id value in the flda of the master table (that is, it exists ).

 

Union all instance details

The purpose of the union command is to combine the results of two SQL statements to view the query results you want.

For example:

 

SQL> select * from;

Id name
--------------------
1 aa
2 bb
3 cc
6 dd
7 ee

SQL> select * from B;

Id addr
--------------------
1 aa
2 bb
3 cc
4 dd
5 ee

SQL> select * from
2 union all
3 select * from B;

Id name
--------------------
1 aa
2 bb
3 cc
6 dd
7 ee
1 aa
2 bb
3 cc
4 dd
5 ee

10 rows have been selected.

SQL> select * from
2 union
3 select * from B;

Id name
--------------------
1 aa
2 bb
3 cc
4 dd
5 ee
6 dd
7 ee

You have selected 7 rows.

SQL>

Note: In union usage, the Field Types of the two select statements must match and the number of fields must be the same. In the preceding example, more complex conditions may occur during the actual software development process.