Oracle-L: Re: Sql query

From: Greg Moore <sqlgreg_at_pacbell.net>
Date: Wed, 26 Jun 2002 14:44:07 -0800
Message-ID: <F001.0048996B.20020626144407@fatcity.com>

2 versions of SQL + TKPROF excerpts

8.1.6, Solaris

SQL version #1

select count(*)
from customers c

, sales s
where c.customer_id = s.customer_id(+)
and s.customer_id is null

count cpu elapsed disk query current rows

total 4 7.33 7.36 1 200352 4 1

Rows Row Source Operation

------- ----------------------------------------
1       SORT AGGREGATE

99105 FILTER
169049 NESTED LOOPS OUTER

100000     INDEX FAST FULL SCAN (object id 22199)
69944      INDEX RANGE SCAN (object id 22232)



----------------------------

SQL version #2

select count(*)
from customers c
where c.customer_id not in (

         select /*+ hash_aj */
                s.customer_id
           from sales s )

       count   cpu elapsed disk query current rows

total      5  1.17    1.94  159   356       8    1

Rows Row Source Operation

------- ------------------------------------------
1       SORT AGGREGATE 
99105    HASH JOIN ANTI 
99999     INDEX FAST FULL SCAN (object id 22199)
69945     VIEW VW_NSO_1 
69945       INDEX FAST FULL SCAN (object id 22232)

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Greg Moore
  INET: sqlgreg_at_pacbell.net

Fat City Network Services    -- (858) 538-5051  FAX: (858) 538-5051
San Diego, California        -- Public Internet access / Mailing Lists


--------------------------------------------------------------------

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 Wed Jun 26 2002 - 17:44:07 CDT