Re: Interview Question on Subqueries vs Regular joins

On Feb 11, 1:41 pm, Charles Hooper <hooperc2..._at_yahoo.com> wrote:
> On Feb 11, 1:07 pm, leonard.reinst..._at_gmail.com 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):http://groups.google.com/group/comp.databases.oracle.misc/browse_thre...
>
> 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:http://groups.google.com/group/comp.databases.oracle.server/browse_th...
>
> 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 Received on Mon Feb 11 2008 - 14:31:49 CST