Re: Interview Question on Subqueries vs Regular joins

From: joel garry <>
Date: Mon, 11 Feb 2008 14:29:22 -0800 (PST)
Message-ID: <>

On Feb 11, 12:31 pm, "" <> wrote:
> On Feb 11, 1:41 pm, Charles Hooper <> wrote:
> > On Feb 11, 1:07 pm, wrote:
> > > > This technical interview question does not make sense.  Oracle _may_
> > > > automatically transform a subquery into an inline view, which then is
> > > > joined to the rest of the query as a "regular join".
> > > Understood. So what would be the general guidelines for a developer to
> > > use "regular" joins as opposed to subqueries? Or does it simply a
> > > matter of coding preference and won't matter from design and
> > > performance perspective?
> > Joel Garry provided a more complete answer, but I will take a stab at
> > the answer...
> > In Oracle 8i and earlier, there was a difference.  In Oracle 8i, the
> > subquery had to be resolved for _each_ row in the resultset as those
> > versions did not perform an automatic transformation, which often
> > yielded poor performance compared to an equivalent query that made use
> > of an inline view.  Oracle 9i, in some/many cases Oracle blindly
> > performs such transformations, even if the cost (expected time) will
> > be greater.  Oracle 10g may perform such transformations if the cost
> > will be lower.
> > A couple months ago I contributed to this thread, in which I compared
> > the performance of the different methods (EXISTS subquery, IN
> > subquery, and inline view):
> > In the above test case, the subquery method, without allowing any
> > automatic transformations, required 46 minutes and 21 seconds,
> > compared to 0.21 seconds when transformations were permitted.
> > Another thread:
> > The best general guideline is to test the performance of the
> > equivalent SQL statements on the version of Oracle that is available.
> > Compare the execution plans - if the execution plans look very
> > similar, Oracle probably transformed one or more of the SQL statements
> > into a more efficient form.
> > I personally prefer the inline view approach with a "regular" join.
> > Note that in some cases, in the inline view the DISTINCT clause must
> > be included to eliminate unintended duplicate result rows when
> > converting a query from having a subquery to a "regular" join, so
> > maybe that is the distinction that the interviewer was hoping that you
> > would identify?
> > Charles Hooper
> > IT Manager/Oracle DBA
> > K&M Machine-Fabricating, Inc.
> It could also be that the interviewer didn't know any more than the
> interviewee and was merely reading questions (and 'answers') found in
> various places on the Internet.
> In my mind the question is too open-ended to allow for a definitive
> answer, as you and others have proven in this thread.  Any
> 'answer' (or 'silver bullet') claimed as definitive is most likely
> suspect.
> My two cents.
> David Fitzjarrell

Hit the jackpot on a two-cent slot: and search for subquery.

Here's one that is perhaps wrong, perhaps the OCP type answer:

Here's one that's stupid:

Here's one that has been removed for some violation:

The idiocy of it being a vulgar sql-server question never even occured to me.

As usual, Charles' answer is top-notch.

Oddly enough, when I read the OP, I was taking a break from writing a fixit sql, which I purposefully want to explode into 150K single statements with joins, since I can't trust the "specs" (many transaction types in a cost accounting system where someone forgot to update costs for the new fiscal year, but only for certain circumstances, which propagated into denormalized tables, and...).

java group (!) removed.


-- is bogus.
"Never trade luck for skill."
Received on Mon Feb 11 2008 - 16:29:22 CST

Original text of this message