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
[EMAIL PROTECTED]
--
Please see the official ORACLE-L FAQ: http://www.orafaq.com
--
Author: Carle, William T (Bill), ALCAS
INET: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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: [EMAIL PROTECTED]
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: [EMAIL PROTECTED] (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).
