what is the difference between union and union all
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.
Is This Answer Correct ? | 9 Yes | 1 No |
Post New Answer View All Answers
what is 'mysqld'? : Sql dba
how to get help information from the server? : Sql dba
1. is it possible to use the cursor atttibutes (%found ,% rowcount , %isopen , %notfound ) to our user defined cursor names ....... cursor cursor_name is select * from scott.emp if you use... cursor_name%found , %rowcount ,%isopen,%notfound...will it work... -------------------------- 2.what is the difference between the varray and index by table .. -------- 3. type type_name is table of number(8,3) index by binary_integer; identifier_name type_name; first , last , prior , next ,trim are the methods we can use it for the above type...simillary is there any way to apply for cursors... with thanks and regards..sarao...
Mention what does the hierarchical profiler does?
What is a file delimiter?
Can triggers stop a dml statement from executing on a table?
what are the advantages and disadvantages of cascading style sheets? : Sql dba
how many sql dml commands are supported by 'mysql'? : Sql dba
What is your daily office routine?
How is data stored on a disk?
how to select unique records from a table? : Sql dba
What is the default isolation level in sql server? : Transact sql
Can a varchar be a primary key?
Is foreign key mandatory?
Which are the different character-manipulation functions in sql?