Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Join vs. Subselect

Re: Join vs. Subselect

From: Thomas Day <tday6_at_csc.com>
Date: Mon, 11 Nov 2002 09:03:45 -0800
Message-ID: <F001.005001F4.20021111090345@fatcity.com>

I'll leave it to others to explain why but it's sometimes faster to do an outer join with a not null:

select whatever from table1, table2+
where table2.whatever is not null;

I've had selects of that form run in seconds where the sub-select or the straight join form took hours.

                                                                                                                   
                    "Carle,                                                                                        
                    William T            To:     Multiple recipients of list ORACLE-L <ORACLE-L_at_fatcity.com>       
                    (Bill),              cc:                                                                       
                    ALCAS"               Subject:     Join vs. Subselect                                           
                    <wcarle                                                                                        
                    @att.com>                                                                                      
                    Sent by: root                                                                                  
                                                                                                                   
                                                                                                                   
                    11/11/2002                                                                                     
                    11:23 AM                                                                                       
                    Please                                                                                         
                    respond to                                                                                     
                    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: Thomas Day INET: tday6_at_csc.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).
Received on Mon Nov 11 2002 - 11:03:45 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US