Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Avoiding UNIONs help

185669Oct 4 2009 — edited Oct 4 2009
Is there a way to avoid a union query (which means two full table scans) for the following:

Here is the set up:
create table x
(id number(2),
leftid number(5),
rightid number(5)
);

insert into x values(1,11111,11111);
insert into x values(2,11111,22222);
insert into x values(3,22222,33333);

And here is the Query:
select id,leftid,'LEFT' side from x
union
select id,rightid,'RIGHT' side from x;


The tables I have are huge and to gain performance, I'd rather not have the table read twice, but still get result set as the union query above... THANKS!
ID LEFTID SIDE
---------- ---------- -----
1 11111 LEFT
1 11111 RIGHT
2 11111 LEFT
2 22222 RIGHT
3 22222 LEFT
3 33333 RIGHT
This post has been answered by Solomon Yakobson on Oct 4 2009
Jump to Answer
Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Nov 1 2009
Added on Oct 4 2009
4 comments
1,120 views