Answer Posted / vishal

UNION removes duplicates, whereas UNION ALL does not.

In order to remove duplicates the result set must be
sorted, and this may have an impact on the performance of
the UNION, depending on the volume of data being sorted,
and the settings of various RDBMS parameters ( For Oracle
PGA_AGGREGATE_TARGET with WORKAREA_SIZE_POLICY=AUTO or
SORT_AREA_SIZE and SOR_AREA_RETAINED_SIZE if
WORKAREA_SIZE_POLICY=MANUAL ).

Basically, the sort is faster if it can be carried out in
memory, but the same caveat about the volume of data
applies.

Of course, if you need data returned without duplicates
then you must use UNION, depending on the source of your
data.

I would have commented on the first post to qualify the "is
much less performant" comment, but have insufficient
reputation (points) to do so.

What is the use of pl/sql table?

1046

---

What will you get by the cursor attribute sql%found?

947

---

How do we accept inputs from user during runtime?

1041

---

How do I run a sql trace?

946

---

What are the benefits of stored procedures?

1046

---

Which query operators in sql is used for pattern matching?

940

---

describe mysql connection using mysql binary. : Sql dba

1045

---

How many commands are there in sql?

1009

---

What is break?

1181

---

Which sql statement is used to return only different values?

984

---

What is scope of pl sql developer in future?

991

---

What does dml mean?

964

---

Does sql require a server?

994

---

What is bind variable in pl sql?

1027

---

Are stored procedures faster than dynamic sql?

1018

---