UNION and UNION ALL return different result !
UNION and UNION ALL operations are used to combine two or more queries in a table.
UNION is best used for retrieving data in a sort-unique way. Oracle will process each data retrieved from the table and compare it if there is double values. UNION operation eliminates the duplicated data from the result set. It is usefull if you want distinct value to be retrieved from both collections. This extra process could takes time.
UNION ALL is best used when you want to join two or more tables without care about data duplication. UNION ALL operation is much faster than UNION because it does not require extra process for removing duplication.
So which one provides better performance? It depends on your need. But when you look at the auto trace result for both of them. On the execution plans, you can notice that UNION operation uses UNION ALL operation in execution. Shortly, it can be said that UNION = UNION ALL + sort operations.
So the best practice for this would be to use UNION ALL instead of UNION (whenever possible).
