OraFAQ Forum: Performance Tuning

Home » RDBMS Server » Performance Tuning » Query (Oracle 9i)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Query [message #481207]

Mon, 01 November 2010 16:33

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

Hi All,

I am facing a problem with query.

In the query we are using UNION .If we run individually then it's working fine.
If we union then the query is executing for long time And we are not getting result .
please help me.
Thanks in advance.

Report message to a moderator

Re: Query [message #481209 is a reply to message #481207]

Mon, 01 November 2010 16:40

Barbara Boehmer
Messages: 9100
Registered: November 2002
Location: California, USA

Senior Member

"Union" automatically removes duplicates like using distinct on the whole result set. This causes a sort that makes it slow. If you do not need to eliminates duplicates then using "union all" is faster.

Report message to a moderator

Re: Query [message #481212 is a reply to message #481207]

Mon, 01 November 2010 16:52

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

See Performance Tuning forum, there are many topics with this question INCLUDIND one posted TODAY.

Regards
Michel

Report message to a moderator

Re: Query [message #481213 is a reply to message #481207]

Mon, 01 November 2010 18:39

ajaykumarkona
Messages: 399
Registered: August 2010

Senior Member

I have to fetch distinct records only

Report message to a moderator

Re: Query [message #481214 is a reply to message #481213]

Mon, 01 November 2010 18:44

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

http://www.orafaq.com/forum/m/433888/136107/#msg_433888
Please refer to URL above & be sure to provide the details requested:

Report message to a moderator

Re: Query [message #481236 is a reply to message #481213]

Tue, 02 November 2010 02:07

Michel Cadot
Messages: 68716
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

ajaykumarkona wrote on Tue, 02 November 2010 00:39

I have to fetch distinct records only

So there is no more question, you want distinct values, you have to use UNION, if you want more help post what is requested in BlackSwan's link.

Regards
Michel

[Updated on: Tue, 02 November 2010 02:11]

Report message to a moderator

Re: Query [message #481251 is a reply to message #481236]

Tue, 02 November 2010 03:50

Roachcoach
Messages: 1576
Registered: May 2010
Location: UK

Senior Member

Make sure your individual "test runs" are actually returning all the rows and not first n.

In what way would you "not get a result" using the union?

Report message to a moderator

Previous Topic:	union in one go
Next Topic:	DBMS_ADVISOR ORA-13699

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Nov 22 07:22:56 CST 2024