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!

Selecting rows where one column values are in other column values

1002123Dec 8 2016 — edited Dec 9 2016

Hi,

I'm using oracle version "Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production".

I am working on an existing vendor supplied query which is performing very slow. So was looking at the query and am trying to see if it can be re written in a better way. Since the query is a complicated and a long one I wont be able to replicate the full query for a test and mention here. SO am looking at chunks and one part of the query is something which I feel can be better written.

I will explain thru an example:

CREATE TABLE GROUP_TEST (GROUP_ID number, GROUP_MEMBER number);

insert into GROUP_TEST values(1,2);

insert into GROUP_TEST values(2,4);

insert into GROUP_TEST values(3,8);

insert into GROUP_TEST values(4,9);

insert into GROUP_TEST values(5,3);

insert into GROUP_TEST values(6,10);

commit;

select * from GROUP_TEST;

pastedImage_0.png

Actually the GROUP_TEST is a complicated query and an inline view in the query. For example sake I tried to mention here as an table so query part will be easier.

We need to find out the Group Ids which are in group_member as well in the above table.The existing query is in this way:

select * from GROUP_TEST where group_id in (select group_member from (select * from GROUP_TEST));

The highlighted part above is a very big query which is used repetitively and in filter clause so it is getting processed for each row from outside view. Can you please suggest if any good way to rewrite this?

Comments
Locked Post
New comments cannot be posted to this locked post.
Post Details
Locked on Jan 6 2017
Added on Dec 8 2016
5 comments
4,331 views