Path: news.easynews.com!easynews!priapus.visi.com!news-out.visi.com!hermes.visi.com!newsfeeds-atl2!news.webusenet.com!pc01.webusenet.com!news4.srv.hcvlny.cv.net.POSTED!53ab2750!not-for-mail
Message-ID: <3D529EB2.8040103@zdnetmail.com>
From: dudester <duderino@zdnetmail.com>
User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:0.9.9) Gecko/20020513
X-Accept-Language: en-us, en
MIME-Version: 1.0
Newsgroups: comp.databases.oracle.server
Subject: Re: Merge Join Cartesian
References: <21e9f79.0208080738.364d5e3c@posting.google.com>
Content-Type: text/plain; charset=us-ascii; format=flowed
Content-Transfer-Encoding: 7bit
Lines: 73
Date: Thu, 08 Aug 2002 12:39:14 -0400
NNTP-Posting-Host: 67.83.104.254
X-Trace: news4.srv.hcvlny.cv.net 1028825143 67.83.104.254 (Thu, 08 Aug 2002 12:45:43 EDT)
NNTP-Posting-Date: Thu, 08 Aug 2002 12:45:43 EDT
Organization: Optimum Online
Xref: easynews comp.databases.oracle.server:157285
X-Received-Date: Thu, 08 Aug 2002 09:42:50 MST (news.easynews.com)

I don't see table e being joined to any of the other tables.  Looks like 
you're getting a cartesian.


April wrote:
> 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