Home » RDBMS Server » Performance Tuning » UNION and UNION ALL discrepancy (Oracle 9i, Solaris)
UNION and UNION ALL discrepancy [message #337913] Fri, 01 August 2008 09:47 Go to next message
bijou
Messages: 6
Registered: August 2008
Location: London
Junior Member
Hello,

I am having trouble with a stored procedure that selects data (using sql statements connected with "UNION ALL") and then outputs the data to a file.

- select with "UNION ALL" takes 6 minutes
- output of 84,669 rows to file takes 36 minutes

If the stored prod is changed to use a "UNION" clause ...

- select with "UNION" takes 21 minutes
- output of 84,422 rows to file takes 2 minutes

Why does the output to file when using "UNION ALL" take so much longer than the output to file when using "UNION" ?

Thanks.
Re: UNION and UNION ALL discrepancy [message #337914 is a reply to message #337913] Fri, 01 August 2008 09:53 Go to previous messageGo to next message
Mahesh Rajendran
Messages: 10672
Registered: March 2002
Location: oracleDocoVille
Senior Member
Account Moderator
could you post the plan for both the statements?
Re: UNION and UNION ALL discrepancy [message #337917 is a reply to message #337913] Fri, 01 August 2008 10:06 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Maybe because the first one loads into cache the data that are also used by the second one.

A useful command: "set autotrace on" or "set autotrace traceonly" if you don't want all the result lines.

Regards
Michel
Re: UNION and UNION ALL discrepancy [message #337918 is a reply to message #337914] Fri, 01 August 2008 10:07 Go to previous messageGo to next message
bijou
Messages: 6
Registered: August 2008
Location: London
Junior Member
Explain plans for UNION and UNION ALL statements posted.
Re: UNION and UNION ALL discrepancy [message #337920 is a reply to message #337917] Fri, 01 August 2008 10:10 Go to previous messageGo to next message
bijou
Messages: 6
Registered: August 2008
Location: London
Junior Member
Michel,

The discrepancy is observed regardless of whether the UNION or UNION ALL version is run first or second, so I do not think this is a cacheing issue.

- Bijou
Re: UNION and UNION ALL discrepancy [message #337924 is a reply to message #337918] Fri, 01 August 2008 10:20 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Format your plan in a readable fashion.

Use "set autotrace traceonly" as I said and post the result.
Before, please read OraFAQ Forum Guide, especially "How to format your post?" section.
Make sure that lines of code do not exceed 80 characters when you format.
Indent the code (See SQL Formatter).
Use the "Preview Message" button to verify.
Also always post your Oracle version (4 decimals).

Regards
Michel
Re: UNION and UNION ALL discrepancy [message #337955 is a reply to message #337913] Fri, 01 August 2008 12:59 Go to previous messageGo to next message
joy_division
Messages: 4641
Registered: February 2005
Location: East Coast USA
Senior Member
What do you mean that the SELECT takes 6 minutes but the output takes 36 minutes.
Re: UNION and UNION ALL discrepancy [message #337963 is a reply to message #337955] Fri, 01 August 2008 14:12 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
joy_division wrote on Fri, 01 August 2008 19:59
What do you mean that the SELECT takes 6 minutes but the output takes 36 minutes.

TOAD I bet. Display first 100 rows is regarded a "select"
Re: UNION and UNION ALL discrepancy [message #338126 is a reply to message #337963] Sun, 03 August 2008 21:45 Go to previous messageGo to next message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
A UNION performs a SORT, which means all of the data is loaded into TEMP. This takes a lot of thinking time, but once the sort is complete, the data can be output rapidly because it is all sitting in TEMP.

A UNION ALL outputs rows as they are found. If your SQL is scanning non-matching rows and then filtering them out, then there may be a wait between rows as it scans and discards unwanted rows.

This could also be observed with an index scan that discards no rows; the cost of scanning an index and retrieving rows from all over the disk is more than a straight scan of a sorted result set in TEMP.

In short, the only thing that will rival the output speed of a TEMP result set is a Full Table Scan without a WHERE clause.

Ross Leishman
Re: UNION and UNION ALL discrepancy [message #338203 is a reply to message #337963] Mon, 04 August 2008 03:39 Go to previous messageGo to next message
bijou
Messages: 6
Registered: August 2008
Location: London
Junior Member
Joy_division / Frank,

What I meant by "The select takes 6 mins and the output takes 36 mins" is this:

From the time the select starts executing till the time the first row of output is written = 6 mins. From that instant, it takes another 36 mins for the rest of the output to be written.





Re: UNION and UNION ALL discrepancy [message #338207 is a reply to message #338126] Mon, 04 August 2008 03:43 Go to previous messageGo to next message
bijou
Messages: 6
Registered: August 2008
Location: London
Junior Member
Rleishman,

Thanks for your reply. That makes a lot of sense.

Do you know if there is a way to force the "UNION ALL" SQL statement to do its work in TEMP as well ?

Re: UNION and UNION ALL discrepancy [message #338210 is a reply to message #338207] Mon, 04 August 2008 03:45 Go to previous messageGo to next message
Michel Cadot
Messages: 64130
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Add an "order by" clause.
But you should investigate more instead of trying to use a "trick". Have a look at your array fetching size.

Regards
Michel
Re: UNION and UNION ALL discrepancy [message #338967 is a reply to message #338210] Wed, 06 August 2008 06:10 Go to previous messageGo to next message
bijou
Messages: 6
Registered: August 2008
Location: London
Junior Member
I appreciate all the comments I have had on this issue. It is clear that the i/o cannot be optimised any further if UNION ALL is to be used.

I would like to investigate the possibility of improving the efficiency of the SQL if possible.

I have posted the query plan for the SQL in question (in a readable format .csv file) - I would welcome any recommendations for performance improvement.


Re: UNION and UNION ALL discrepancy [message #339604 is a reply to message #338967] Thu, 07 August 2008 21:52 Go to previous message
rleishman
Messages: 3724
Registered: October 2005
Location: Melbourne, Australia
Senior Member
Run each of the components of the UNION ALL individually. Find out if one of them is less efficient than the others.

Post the SQL of that ONE here. Run a SQL Trace and post the TKPROF output here too. Enclose it in CODE tags to make it display properly - we don't like opening attachments.

Ross Leishman
Previous Topic: Index is used still problem
Next Topic: How to simplify this pivot query
Goto Forum:
  


Current Time: Wed Dec 07 08:51:43 CST 2016

Total time taken to generate the page: 0.09587 seconds