Home » SQL & PL/SQL » SQL & PL/SQL » Impact of UNION ALL of nearly 10 SQLs on performance ? (Oracle 10g)
Impact of UNION ALL of nearly 10 SQLs on performance ? [message #313723] Mon, 14 April 2008 05:28 Go to next message
hidnana
Messages: 87
Registered: November 2007
Location: chennai
Member
I currently have a very big SQL which are actually set of different SQLs (10 SQLs) UNION ALLed together.
Will there be any performance impact on account of this ?
How will the individual SQLs bound together by UNION ALL run, one after another or in parallel.
Should any hint be specified which addresses this scenario ?
Any other alternatives to UNION/UNION ALL ?
Re: Impact of UNION ALL of nearly 10 SQLs on performance ? [message #313804 is a reply to message #313723] Mon, 14 April 2008 10:50 Go to previous messageGo to next message
S.Rajaram
Messages: 1027
Registered: October 2006
Location: United Kingdom
Senior Member
To start with why don't you look at the guidelines mentioned under the performance tuning section. It is detailed enough to address most of your question you have posted.

Regards

Raj
Re: Impact of UNION ALL of nearly 10 SQLs on performance ? [message #313847 is a reply to message #313804] Mon, 14 April 2008 21:47 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
There is no impact of UNION ALL over-and-above just running the individual SQLs one after the other, unless you include an ORDER BY or you use UNION instead of UNION ALL.

Oracle generally does not parallelise like you might imagine with UNION ALL. If it does parallelize, it is more likely that each sub-SQL will be executed one after the other with parallelism applied to the underlying processes of the individual SQL. But there are no guarantees here - Oracle does not commit to processing the SQLs in a UNION ALL in any order.

You will only come unstuck if you try to treat a UNION ALL query as a view (or inline view) and join it to some other table, or use it in a sub-query.

Used on it's own, there are no surprises.

Ross Leishman
Previous Topic: Problem with procedure having exceptions
Next Topic: regexp and oracle 10g
Goto Forum:
  


Current Time: Thu Dec 08 16:41:27 CST 2016

Total time taken to generate the page: 0.06699 seconds