Home » SQL & PL/SQL » SQL & PL/SQL » Behaviur of UNION (Oracle 10.2.0.1, XP)
Behaviur of UNION [message #377098] Sat, 20 December 2008 02:33 Go to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Hi all,

I have a general question regarding behaviour of UNION.
It is not related to particular table or its data.
and hence, I am not providing table or its content.

When I use UNION operator, it gives whole result in sorted order.
why it is so?
and if I want to change that behaviour, then what should I do?

regards,
Delna
Re: Behaviur of UNION [message #377099 is a reply to message #377098] Sat, 20 December 2008 02:38 Go to previous messageGo to next message
trivendra
Messages: 208
Registered: October 2007
Location: Noida, India
Senior Member
Use UNION ALL.
Because UNION Sort the data and do distinct.

[Updated on: Sat, 20 December 2008 02:40]

Report message to a moderator

Re: Behaviur of UNION [message #377100 is a reply to message #377098] Sat, 20 December 2008 02:49 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
At one place I read that UNION is faster than UNION ALL.
Is it so?
and why?

regards,
Delna
Re: Behaviur of UNION [message #377101 is a reply to message #377098] Sat, 20 December 2008 02:56 Go to previous messageGo to next message
flyboy
Messages: 1832
Registered: November 2006
Senior Member
Quote:
why it is so?

As UNION eliminates rows duplicate in all columns (as documented in http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/queries004.htm#i2054381), it may use some kind of sorting algorithm. See similar post for GROUP BY behaviour by Ross Leishman: http://www.orafaq.com/forum/m/236522/0/?srch=GROUP+BY#msg_236522.
Quote:
and if I want to change that behaviour, then what should I do?

If you want to include duplicates and/or the result cannot contain them, use UNION ALL. Otherwise specify the desired order in ORDER BY clause. Anyway, without ORDER BY clause the resultset does not have guaranteed order, so it may differ from execution to execution. Are you aware of this?
Re: Behaviour of UNION [message #377103 is a reply to message #377101] Sat, 20 December 2008 03:22 Go to previous messageGo to next message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
thanks for reply.

Quote:

without ORDER BY clause the resultset does not have guaranteed order, so it may differ from execution to execution. Are you aware of this?



From the links provided in previous post, I knew that it does not give perfect sorted data.
so are there any parameters for that?

regards,
Delna
Re: Behaviour of UNION [message #377105 is a reply to message #377103] Sat, 20 December 2008 03:38 Go to previous messageGo to next message
Michel Cadot
Messages: 64139
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Quote:
so are there any parameters for that?

Think a little bit.
Why should there be a parameter to make a feature fulfil another feature?

Regards
Michel
Re: Behaviour of UNION [message #377106 is a reply to message #377105] Sat, 20 December 2008 03:41 Go to previous message
delna.sexy
Messages: 941
Registered: December 2008
Location: Surat, The Diamond City
Senior Member
Thanks to all for their valuable reply.

regards,
Delna
Previous Topic: PRAGMA
Next Topic: How to pass multiple values through a single variable?
Goto Forum:
  


Current Time: Thu Dec 08 18:00:12 CST 2016

Total time taken to generate the page: 0.10958 seconds