RE: count(*) = 0 and I still need the row

That is, of course, the rule optimizer. Using hints and hash join (two tables being accessed through full table scans and joined using hash instead of sort/merge) the performance can surely be further improved.

-----Original Message-----
From: Suri, Deepak [mailto:DSuri_at_oxhp.com] Sent: Thursday, November 16, 2000 5:49 PM To: Multiple recipients of list ORACLE-L Subject: RE: count(*) = 0 and I still need the row

>>>Anyone know why the the date test alters the outer join results?

Dave,

Look at the explain plans below without and with the date clause respectively and you will notice that in the second case the date clause is applied before the "group by" which excludes all rows older than one month (including the "twomonthsago")

Execution Plan -- without date clause

   0 SELECT STATEMENT Optimizer=RULE    1 0 SORT (GROUP BY)

   2    1     MERGE JOIN (OUTER)
   3    2       SORT (JOIN)
   4    3         TABLE ACCESS (FULL) OF 'HP'
   5    2       SORT (JOIN)
   6    5         TABLE ACCESS (FULL) OF 'PV'

Execution Plan -- with date clause

   0 SELECT STATEMENT Optimizer=RULE    1 0 SORT (GROUP BY)

   2    1     FILTER
   3    2       MERGE JOIN (OUTER)
   4    3         SORT (JOIN)
   5    4           TABLE ACCESS (FULL) OF 'HP'
   6    3         SORT (JOIN)
   7    6           TABLE ACCESS (FULL) OF 'PV'

hope this resolves your dilemma !!

deepak

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.com
-- 
Author: Suri, Deepak
  INET: DSuri_at_oxhp.com

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