Path: news.easynews.com!easynews!news.he.net!news-hog.berkeley.edu!ucberkeley!newsfeed.stanford.edu!postnews1.google.com!not-for-mail
From: privatebenjamin@hushmail.com (April)
Newsgroups: comp.databases.oracle.server
Subject: Merge Join Cartesian
Date: 8 Aug 2002 08:38:49 -0700
Organization: http://groups.google.com/
Lines: 66
Message-ID: <21e9f79.0208080738.364d5e3c@posting.google.com>
NNTP-Posting-Host: 142.46.127.2
Content-Type: text/plain; charset=ISO-8859-1
Content-Transfer-Encoding: 8bit
X-Trace: posting.google.com 1028821130 476 127.0.0.1 (8 Aug 2002 15:38:50 GMT)
X-Complaints-To: groups-abuse@google.com
NNTP-Posting-Date: 8 Aug 2002 15:38:50 GMT
Xref: easynews comp.databases.oracle.server:157279
X-Received-Date: Thu, 08 Aug 2002 08:35:54 MST (news.easynews.com)

Hi,

Oracle version 7.3.4

I am having problems with what should be a simple query. The access
path shows a Merge Join Cartesian and the query never completes.  As
far as I can tell there are no missing joins.

THe query:
SELECT DISTINCT
  a.INV_DRIVER#,
  b.INVESTMENT#,
  c.Release#
FROM 
  vw_investment_drivers a,
  vw_me_Investment_Table b,
  vw_me_subinvestment_desc c,
  vw_me_releases d,
  vw_me_release_detail e
WHERE 
  a.INV_DRIVER# = b.INV_DRIVER# and
  b.INVESTMENT# = c.INVESTMENT# and
  c.RELEASE# = d.RELEASE# and
  c.SUB_INVESTMENT# = d.SUB_INVESTMENT# and
  c.INVESTMENT# = d.INVESTMENT# and
  c.O7MRA_STATUS = -1 AND
  c.PLANNED_RELEASE_DATE < ADD_MONTHS(Sysdate, 7) AND
  e.Approval_Meeting_Level Is Not Null AND
  c.STATUS_CODE IN (1,2,Null)

Each of the 'tables' listed in the from clause are views which select
the maximum effective dated records.

The explain plan:

Operation	Object Name	Rows	Bytes	Cost	Object Node	In/Out	PStart	PStop

SELECT STATEMENT Hint=CHOOSE		6  	 	37  	 	 	 	 
  SORT UNIQUE		  	 	 	 	 	 	 
    FILTER		  	 	 	 	 	 	 
      MERGE JOIN CARTESIAN		6  	1 K	34  	 	 	 	 
        NESTED LOOPS		1  	207  	25  	 	 	 	 
          NESTED LOOPS		1  	177  	24  	 	 	 	 
            NESTED LOOPS		1  	152  	22  	 	 	 	 
              TABLE ACCESS FULL	TBLINVESTMENT_TABLE	6  	312  	4
              TABLE ACCESS BY ROWID	TBLSUB_INVESTMENT_DESCRIPT	1  	100
 	24
                INDEX RANGE SCAN	SUB_INV_STATUS_CODE	  	 	 	 	 	 	 
                  SORT AGGREGATE		  	 	 	 	 	 	 
                    INDEX RANGE SCAN	SUB_INV_STATUS_CODE	1  	48  	2
            INDEX RANGE SCAN	TBLRELEASES_PK	15  	375  	4  	 	 	 	 
              SORT AGGREGATE		  	 	 	 	 	 	 
                INDEX RANGE SCAN	TBLRELEASES_PK	1  	25  	2  	 	 	 	 
          TABLE ACCESS BY ROWID	TBLINVESTMENT_DRIVERS	21  	630
            INDEX RANGE SCAN	TBLINVESTMENT_DRIVERS_ME	  	 	 	 	 	 	 
        SORT JOIN		  	 	 	 	 	 	 
          TABLE ACCESS FULL	TBLRELEASE_DETAIL	6  	288  	 	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        INDEX RANGE SCAN	TBLRELEASE_DETAIL_PK	2  	44  	2  	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        TABLE ACCESS FULL	TBLINVESTMENT_DRIVERS	2  	26  	1  	 	 	 	 
      SORT AGGREGATE		  	 	 	 	 	 	 
        INDEX RANGE SCAN	SYS_C001011	2  	44  	2  	 	 	 	 

Thanks for your help,
April