Re: Interview Question on Subqueries vs Regular joins

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_thread/thread/6b51cb4e0c26256b

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_thread/thread/db317ea2faa1a7d0

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. Received on Mon Feb 11 2008 - 13:41:17 CST