Home » SQL & PL/SQL » SQL & PL/SQL » Tuning Code Performance - UNION ALL (oracle db 10g)
Tuning Code Performance - UNION ALL [message #425668] Sun, 11 October 2009 07:40 Go to next message
aryanna
Messages: 44
Registered: July 2007
Member

Hi All,

I m looking for any other way that i can code without using union all for my case.

my data example is like this

table1
--------
col1 col2 Segment1 Segment2 Segment3
-------------------------------------------------------------
A11 B11 John Jhonny Johnathan
A12 B12 Melisa Amy Abagial



I need to create view of above record as below:

table2
------
col1 col2 col3 col4
--------------------------------------------------------------
A11 B11 Segment1 John
A11 B11 Segment2 Jhonny
A11 B11 Segment3 Johnathan
A12 B12 Segment1 Melisa
A12 B12 Segment2 Amy
A12 B12 Segment3 Abagial


now my code is using UNION ALL to get output as in table2


select col1,col1,'Segment1' col3,Segment1 col4 from table1
union all
select col1,col1,'Segment2' col3,Segment2 from table1
union all
select col1,col1,'Segment3' col3,Segment3 from table1

But the problem is the performance is realy bad.
Is there any way i can do this without using union all?
The time that take to execute this is not exceptable.

Need Help Sad

Re: Tuning Code Performance - UNION ALL [message #425670 is a reply to message #425668] Sun, 11 October 2009 08:44 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
This is a classic columns to rows query.
Use a row generator up to the number of columns.


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 code tags and align the columns in result.
Use the "Preview Message" button to verify.
Also always post your Oracle version with 4 decimals.

Post a working Test case: create table and insert statements along with the result you want with these data.

Regards
Michel
Re: Tuning Code Performance - UNION ALL [message #425836 is a reply to message #425668] Mon, 12 October 2009 07:43 Go to previous messageGo to next message
Kevin Meade
Messages: 2101
Registered: December 1999
Location: Connecticut USA
Senior Member
In combination with yoru decode/case used to do your data pivots, read up on row generation techinques. They will allow you to duplicate a row (in your case three times) which will allow you to read data from TABLE1 only once. Consider how the blow might aid you.

SQL> select level lvl
  2  from dual
  3  connect by level <= 3
  4  /

       LVL
----------
         1
         2
         3

3 rows selected.

SQL> 

Good luck, Kevin

[Updated on: Mon, 12 October 2009 07:45]

Report message to a moderator

Re: Tuning Code Performance - UNION ALL [message #489731 is a reply to message #425836] Tue, 18 January 2011 11:46 Go to previous messageGo to next message
aryanna
Messages: 44
Registered: July 2007
Member

Thanks you Kelvin. That what i need.
Re: Tuning Code Performance - UNION ALL [message #489732 is a reply to message #489731] Tue, 18 January 2011 12:06 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Wow! 15 months to feedback, you have improved your response time!

Regards
Michel

[Updated on: Tue, 18 January 2011 12:06]

Report message to a moderator

Previous Topic: running query with instr function causes ORA-03113: end-of-file on communication channel
Next Topic: loader* and insert in ORACLE
Goto Forum:
  


Current Time: Sun Dec 04 12:38:25 CST 2016

Total time taken to generate the page: 0.09642 seconds