Oracle-L: RE: Join vs. Subselect

From: Carle, William T (Bill), ALCAS <wcarle_at_att.com>
Date: Wed, 13 Nov 2002 06:03:52 -0800
Message-ID: <F001.00501F3B.20021113060352@fatcity.com>

Dan,

Actually, there is an index on the objid within the subselect. Here is the actually query with the results of the tkprof:

SELECT ObjId, ActionCode, RequestNumber, AbcDigits, DefDigits, SoClli, NodalSource, StatusCode, ReplacedRequestNumber, TransactionId, Domain, FinalHandlingTreatment, ServiceType, DivisionRevenueCode, StatusMessage, EffectiveDateTime, CreateDateTime, ProvisionStartDateTime,ProvisionCompleteDateTime, UserId, Notes, Version from ILSRONodalRequest where statusCode != 'X' and objid in ( select unique NodalRequest_ObjId from ILSRONodalRoute where messageRouteClli = 'CHRLNCLQN02') order by effectiveDateTime desc, createDateTime desc

call count cpu elapsed disk query current rows ------- ------ -------- ---------- ---------- ---------- ---------- ----------

Parse        1      0.05       0.04          0          0          0           0
Execute      2      0.00       0.00          0          0          0           0
Fetch        2      2.50     108.18       5470       4660          3           2
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        5      2.55     108.22       5470       4660          3           2

Misses in library cache during parse: 1
Optimizer goal: CHOOSE
Parsing user id: 29 (RCONBIG)

Rows Row Source Operation

-------  ---------------------------------------------------
      2  SORT ORDER BY
      2   HASH JOIN SEMI
 146858    TABLE ACCESS FULL ILSRONODALREQUEST
      2    TABLE ACCESS BY INDEX ROWID ILSRONODALROUTE
      2     INDEX RANGE SCAN (object id 6393)


Rows     Execution Plan
-------  ---------------------------------------------------
      0  SELECT STATEMENT   GOAL: CHOOSE
      2   SORT (ORDER BY)
      2    NESTED LOOPS
 146858     VIEW OF 'VW_NSO_1'
      2      SORT (UNIQUE)
      2       TABLE ACCESS (BY INDEX ROWID) OF 'ILSRONODALROUTE'
      0        INDEX (RANGE SCAN) OF 'IX_ILSRONODALROUTE3'
                   (NON-UNIQUE)
      0     TABLE ACCESS (BY INDEX ROWID) OF 'ILSRONODALREQUEST'
      0      INDEX (UNIQUE SCAN) OF 'IX_ILSRONODALREQUEST' (UNIQUE)

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

-----Original Message-----

Sent:	Tuesday, November 12, 2002 4:39 PM
To:	Multiple recipients of list ORACLE-L
Subject:	RE: Join vs. Subselect

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
---------------------------------------------------------------------

--

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
---------------------------------------------------------------------