Re: Interview Question on Subqueries vs Regular joins

On Feb 11, 12:31 pm, "fitzjarr..._at_cox.net" <fitzjarr..._at_cox.net> wrote:
> 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

Hit the jackpot on a two-cent slot:
http://blog.sqlauthority.com/2007/04/20/sql-server-interview-questions-part-6/ and search for subquery.

Here's one that is perhaps wrong, perhaps the OCP type answer: http://matrixl.net/oradev/intrv01.htm

Here's one that's stupid: http://www.geekinterview.com/question_details/62777

Here's one that has been removed for some violation: http://209.85.173.104/search?q=cache:RhKiKCJHBQkJ:www.itinterviewquestion.com/ms-sql-server-interview-question/+when+would+you+use+subqueries+and+when+would+you+use+regular+joins+oracle+interview+question&hl=en&ct=clnk&cd=12&gl=us

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.

jg

--
@home.com is bogus.
"Never trade luck for skill."