Re: Nested queries V/S Joins

In article <4cgrcq$l7m@cssun.mathcs.emory.edu> bw000001@pixie.co.za (Billy Wheeler) writes: >From: bw000001@pixie.co.za (Billy Wheeler) >Subject: Re: Nested queries V/S Joins >Date: 4 Jan 1996 10:23:06 -0500 >> What is more efficient nested subqueries or joins? >Depends. >>Query 1: >> >> select item from a, b >> where a.item = b.item and >> b.num = 50;>> >>Query 2: >> >> select item from a >> where item in (select item from b where b.num = 50);>> >>If I "set explain on" on the above two queries, Query 1 has a higher >>estimated cost than Query 2, but the execution times (reported by time) >>are less for Query 1. >What you will find if you put a lot of data into tables a and b is that Q2 >will run faster and generally return more consistent response times. Q1 will >degrade consistently over time. Depending on indexes, YMMV, etc. A join should always be faster. However, it is very important taht the indices be up to date i.e. update statistics is very important. Also on some versions, the order that the indices are built is very important. The optimizer does not always use the right index and some times it uses the first index that matches instead of the right one. >>What does the "estimated cost of a query" mean anyway? >It's a pretty arbitrary indication of how long the query should take. In >general, the bigger the number, the longer a query will take (allegedly), >but there doesn't seem to be any way of translating the number into time. The estimated cost of a query is not translatable into time. But, if you optimize a query's estimated cost and lower it, then it will be definitely faster. The query path is a much more accurate indicator. The "ECoQ" is an indicator of how much disk I/O has to happen. >HTH. >Billy Michel Behna - mbehna@promus.com AMA #453810 - DoD #1821 - Suzuki Katana 600