Home » SQL & PL/SQL » SQL & PL/SQL » UNION
UNION [message #441337] Sat, 30 January 2010 23:33 Go to next message
prakashaa
Messages: 31
Registered: November 2009
Location: Bangalore
Member
Hi all,

If we write a query in the following way it hampers the performance.

SELECT id, first_name
FROM student_details_class10
UNION
SELECT id, first_name
FROM sports_team

So instead of that i have used UNION ALL

SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team

But i do not want duplicate rows , how can we write the query in a better performance way.
Please help me
Thanks in advance
Re: UNION [message #441356 is a reply to message #441337] Sun, 31 January 2010 00:26 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
If you don't want duplicates you have to use UNION.

Regards
Michel
Re: UNION [message #441431 is a reply to message #441337] Mon, 01 February 2010 02:04 Go to previous messageGo to next message
elaiyavel
Messages: 114
Registered: April 2008
Senior Member
There will not be any performance variation between UNION and UNION ALL.
Re: UNION [message #441432 is a reply to message #441431] Mon, 01 February 2010 02:10 Go to previous messageGo to next message
Michel Cadot
Messages: 64133
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
UNION ALL do not remove duplicates, UNION does, you can't compare performances between the 2 as they do not do the same thing.

Regards
Michel
Re: UNION [message #441436 is a reply to message #441337] Mon, 01 February 2010 02:43 Go to previous messageGo to next message
rahulvb
Messages: 924
Registered: October 2009
Location: Somewhere Near Equator.
Senior Member
I am under impression that there is difference in performance if user wants unique records. in my view distinct will be advantage.


create table uni_test as select table_name from dba_tables where table_name like '%$%';

set autotrace on 



select distinct table_name from uni_test where table_name like 'X%'
union all
select distinct table_name from uni_test where table_name like 'Y%'

----------------------------------------
| Id  | Operation           | Name     |
----------------------------------------
|   0 | SELECT STATEMENT    |          |
|   1 |  UNION-ALL          |          |
|   2 |   SORT UNIQUE       |          |
|*  3 |    TABLE ACCESS FULL| UNI_TEST |
|   4 |   SORT UNIQUE       |          |
|*  5 |    TABLE ACCESS FULL| UNI_TEST |
----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TABLE_NAME" LIKE 'X%')
   5 - filter("TABLE_NAME" LIKE 'Y%')

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        159  bytes sent via SQL*Net to client
        240  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          2  sorts (memory)
          0  sorts (disk)
          0  rows processed


select table_name from uni_test where table_name like 'X%'
    union 
select table_name from uni_test where table_name like 'Y%'
/


Execution Plan
----------------------------------------------------------
Plan hash value: 2301318611

----------------------------------------
| Id  | Operation           | Name     |
----------------------------------------
|   0 | SELECT STATEMENT    |          |
|   1 |  SORT UNIQUE        |          |
|   2 |   UNION-ALL         |          |
|*  3 |    TABLE ACCESS FULL| UNI_TEST |
|*  4 |    TABLE ACCESS FULL| UNI_TEST |
----------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - filter("TABLE_NAME" LIKE 'X%')
   4 - filter("TABLE_NAME" LIKE 'Y%')

Note
-----
   - rule based optimizer used (consider using cbo)


Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
         10  consistent gets
          0  physical reads
          0  redo size
        168  bytes sent via SQL*Net to client
        240  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          1  sorts (memory)
          0  sorts (disk)
          0  rows processed

Re: UNION [message #441442 is a reply to message #441436] Mon, 01 February 2010 03:33 Go to previous messageGo to next message
ramoradba
Messages: 2454
Registered: January 2009
Location: AndhraPradesh,Hyderabad,I...
Senior Member
i did n` get this proper. Why this distinct here?

OP does n`t want duplicate rows!

I won`t agree with your responce....
I totally agree with Michel.

What Exactly you mean By providing the combination of distinct and union all doesn`t provide duplicate rows?


sriram Smile

[Updated on: Mon, 01 February 2010 03:38]

Report message to a moderator

Re: UNION [message #441445 is a reply to message #441436] Mon, 01 February 2010 04:04 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
@Rahulvb - your query does not do what you think it does.

Specifically, it doesn't return the same set of rows as either the UNION or the UNION ALL queries that were posted at the start.

Here's an example:
create table test_140 (id number);
create table test_141 (id number);

insert into test_140 values (1);
insert into test_140 values (1);
insert into test_140 values (2);
insert into test_140 values (5);
insert into test_140 values (6);

insert into test_141 values (1);
insert into test_141 values (2);
insert into test_141 values (2);
insert into test_141 values (3);
insert into test_141 values (5);

SQL> select id from test_140
  2  union
  3  select id from test_141;

        ID
----------
         1
         2
         3
         5
         6

SQL> 
SQL> select id from test_140
  2  union all
  3  select id from test_141;

        ID
----------
         1
         1
         2
         5
         6
         1
         2
         2
         3
         5

10 rows selected.

SQL> 
SQL> select distinct id from test_140
  2  union all
  3  select distinct id from test_141;

        ID
----------
         1
         6
         2
         5
         1
         2
         5
         3

8 rows selected.


If you think about it for a second, you'll see that your query only ever removes duplicates rom each individual set of rows - it never looks for values that are duplicated in both sets of rows.
Re: UNION [message #441514 is a reply to message #441337] Mon, 01 February 2010 14:01 Go to previous message
Bill B
Messages: 1484
Registered: December 2004
Senior Member
Union all is much faster then union. But how about

select distinct id,first_name
from
(
SELECT id, first_name
FROM student_details_class10
UNION ALL
SELECT id, first_name
FROM sports_team);


Though the optimizer might change it to a union anyway.
Previous Topic: Databse Objects
Next Topic: converting a string to date
Goto Forum:
  


Current Time: Thu Dec 08 03:50:15 CST 2016

Total time taken to generate the page: 0.07120 seconds