Path: news.netfront.net!ctu-peer!news.nctu.edu.tw!news.glorb.com!postnews.google.com!d4g2000prg.googlegroups.com!not-for-mail From: joel garry Newsgroups: comp.databases.oracle.server Subject: Re: Interview Question on Subqueries vs Regular joins Date: Mon, 11 Feb 2008 14:29:22 -0800 (PST) Organization: http://groups.google.com Lines: 111 Message-ID: <9f8e5f97-1640-4cc7-8c3f-c2ae2274d9ad@d4g2000prg.googlegroups.com> References: <1205f03e-cdde-4310-b1f2-ccc7b5a4f504@d70g2000hsb.googlegroups.com> <1df0981c-4efd-4f73-84bb-6c52b308b227@u10g2000prn.googlegroups.com> <33a05477-0fb1-42b3-9a99-157baf58d1cc@i12g2000prf.googlegroups.com> <1ff82251-f0d3-4f18-af49-49fd7e00fecc@v17g2000hsa.googlegroups.com> NNTP-Posting-Host: 75.49.200.201 Mime-Version: 1.0 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: quoted-printable X-Trace: posting.google.com 1202768963 4552 127.0.0.1 (11 Feb 2008 22:29:23 GMT) X-Complaints-To: groups-abuse@google.com NNTP-Posting-Date: Mon, 11 Feb 2008 22:29:23 +0000 (UTC) Complaints-To: groups-abuse@google.com Injection-Info: d4g2000prg.googlegroups.com; posting-host=75.49.200.201; posting-account=tpQovAkAAABNoH5bwsZAiff2L0zxGwdv User-Agent: G2/1.0 X-HTTP-Via: 1.0 ISA2K4-OC1 X-HTTP-UserAgent: Mozilla/4.0 (compatible; MSIE 6.0; Windows NT 5.1; SV1),gzip(gfe),gzip(gfe) Xref: news.netfront.net comp.databases.oracle.server:182348 On Feb 11, 12:31=A0pm, "fitzjarr...@cox.net" wrote: > On Feb 11, 1:41=A0pm, Charles Hooper wrote: > > > > > > > On Feb 11, 1:07=A0pm, leonard.reinst...@gmail.com wrote: > > > > > This technical interview question does not make sense. =A0Oracle _ma= y_ > > > > automatically transform a subquery into an inline view, which then i= s > > > > 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. =A0In 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. =A0Oracle 9i, in some/many cases Oracle blindly > > performs such transformations, even if the cost (expected time) will > > be greater. =A0Oracle 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.serv= er/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. =A0Any > '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/627= 77 Here's one that has been removed for some violation: http://209.85.173.104/search?q=3Dcache: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=3Den&ct=3Dclnk&cd= =3D12&gl=3Dus 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."