Re: Optimization question: Unrolling subquery into IN clause

From: Jeff <dontspam_at_home.nl>
Date: Fri, 16 Oct 2009 12:41:35 +0200
Message-ID: <MPG.25426c2cfedbb1b498968b_at_textnews.eweka.nl>

In article <Xns9CA6680BD4E25wrgrpde_at_news.albasani.net>, wr_at_spam.la says...
> I have a query that looks roughly like this:
>
> SELECT several_columns
> FROM tab1
> WHERE col1='Value'
> AND col2 IN
> (
> SELECT key
> FROM tab2
> WHERE tab2.col3='Something'
> );
>

Have you tried rewriting to:

SELECT several_columns
FROM tab1
WHERE col1='Value'
AND EXISTS
(

	SELECT key
	FROM tab2
	WHERE tab2.col3='Something'
	AND tab1.col2=tab2.key

);

I do not recall the exact details but there is a class of queries where IN() is more efficient and another class where EXISTS() is more efficient even though you would hope Oracle could figure that out itself. Received on Fri Oct 16 2009 - 05:41:35 CDT