Path: news.easynews.com!easynews!news-out.visi.com!hermes.visi.com!sn-xit-05!sn-xit-06!sn-post-01!supernews.com!corp.supernews.com!not-for-mail
From: Sybrand Bakker <postbus@sybrandb.demon.nl>
Newsgroups: comp.databases.oracle.server
Subject: Re: Merge Join Cartesian
Date: Thu, 08 Aug 2002 20:06:03 +0200
Organization: Posted via Supernews, http://www.supernews.com
Message-ID: <flc5lugimbnqs0dlv7379jj1l9kbna65rj@4ax.com>
Reply-To: postbus@sybrandb.demon-verwijderdit.nl
References: <21e9f79.0208080738.364d5e3c@posting.google.com>
X-Newsreader: Forte Agent 1.91/32.564
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii
Content-Transfer-Encoding: 7bit
X-Complaints-To: newsabuse@supernews.com
Lines: 81
Xref: easynews comp.databases.oracle.server:157292
X-Received-Date: Thu, 08 Aug 2002 11:03:23 MST (news.easynews.com)

On 8 Aug 2002 08:38:49 -0700, privatebenjamin@hushmail.com (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


Do you think anyone in the group can guess what the primary and
foreign keys are or people should assemble for a seance to retrieve
them?

Regards


Sybrand Bakker, Senior Oracle DBA

To reply remove -verwijderdit from my e-mail address