Oracle-L: RE: Join vs. Subselect

From: Fink, Dan <Dan.Fink_at_mdx.com>
Date: Tue, 12 Nov 2002 14:39:22 -0800
Message-ID: <F001.005018C9.20021112143922@fatcity.com>

Bill,

Without seeing the explain plans, I'll take a shot. I hope it makes sense...

1 assumption, which if incorrect invalidates everything. I assume that there is not an index on table2.objid.

I think the issue is how statement 1 is executed. Since the subquery is not-correlated (IN instead of an equality condition), it is executed 1 time as a full table scan with the result set stored in memory (or disk if not sufficient room). The intent of the subquery is to build a result set that is compared in total to the output of the outer statement. Once the table1.objid is located in the result set of table2.objid, the condition evaluates to TRUE and no further reading of table2 result set is required. It sounds like it is doing a nested loop operation against data that is already in memory.

In statement2, for each row in table1, matching data in table2 is to be retrieved, perhaps requiring multiple disk reads. In this case, it may be more efficient to use the index. We now have an equality condition, which probably results in a nested loop operation against a table and not a result set in memory.

	Hmm...clear as mud?
	In deference to Cary, Anjo, Gaja, Kirti, Tim, et.al., Which query

runs faster and performs fewer LIOs? That's the true measure of which is the better one.

Dan Fink

-----Original Message-----
Sent: Monday, November 11, 2002 9:24 AM
To: Multiple recipients of list ORACLE-L

Hi,

Here is the situation. The application coded a query that looks like this:

select * from table1
where objid in (select objid from table2);

There is an index on objid in table 1 that isn't being used. An explain shows it is using this system view vw_nso_1 that is used to transform an IN subquery to a join. If you recode the query to:

select a.* from table1 a, table2 b
where a.objid = b.objid;

Then it will use the index. My question is: shouldn't it use the index in both cases. I know the join is a better way to code it and I have told the application that, but I would think that the first way would use an index anyway. Ideas?

Bill Carle
AT&T
Database Administrator
816-995-3922
wcarle_at_att.com

--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Carle, William T (Bill), ALCAS
INET: wcarle_at_att.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

To REMOVE yourself from this mailing list, send an E-Mail message to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in the message BODY, include a line containing: UNSUB ORACLE-L (or the name of mailing list you want to be removed from). You may also send the HELP command for other information (like subscribing).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.com
--

Author: Fink, Dan
INET: Dan.Fink_at_mdx.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------