Re: Interview Question on Subqueries vs Regular joins

On Feb 11, 8:44 am, leonard.reinst..._at_gmail.com wrote:
> Hi!
>
> I was asked the following question at a technical interview: when
> would you use subqueries and when would you use regular joins (pros
> and cons of each approach in terms of design and performance)?
>
> I have read several articles on the subject but could not find a good
> answer to this question.
>
> Any help would be appreciated.
>
> Thanks!

Holy smokes! People have written entire _books_ about that question. I would just throw up my hands and shake my head and say I guess I'm not good enough for that job.

Start here: http://www.google.com/search?hl=en&q=subqueries+joins+%22jonathan+lewis%22&btnG=Google+Search http://www.google.com/search?hl=en&q=subqueries+joins+site%3Aasktom.oracle.com

On the other hand, they may just be looking for how you approach such problems, and the answer might be something like there isn't any general rule of thumb that will hold up over time, you must start with a properly normalized design, and thorough testing, examination of plans and tracing where necessary should elicit where specific coding styling should be done. After all, we should be giving enough information to the optimizer to figure out what it has to do, that is the point of non-procedural languages, after all.

On the third hand, they may have just come back from some presentation where someone gave specific examples of the limitations of the optimizer, and be expecting you to know about that.

On the fourth hand, they may have seen some rules of thumb about the subject, and be expecting that answer.

On the fifth hand, they may be looking for whether you can describe how to prune queries more specifically with the subqueries, and be expecting a discussion of various filtering techniques, and perhaps a discussion of correlation between the parts of a query, unnesting, what Oracle does to process a query, and maybe even ref cursor usage. On the join side, they may be expecting this: http://download.oracle.com/docs/cd/B19306_01/server.102/b14211/optimops.htm#i51523

I don't interview well. I seem to have much better luck just going in and doing the work.

jg

--
@home.com is bogus.
http://www.sfgate.com/cgi-bin/blogs/sfgate/category?blogid=37&cat=1428