Tuning Code Performance - UNION ALL [message #425668] |
Sun, 11 October 2009 07:40  |
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
|
|
|
|
Re: Tuning Code Performance - UNION ALL [message #425836 is a reply to message #425668] |
Mon, 12 October 2009 07:43   |
 |
Kevin Meade
Messages: 2103 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
|
|
|
|
|