Merge Join Cartesian

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#

  vw_investment_drivers a,
  vw_me_Investment_Table b,
  vw_me_subinvestment_desc c,
  vw_me_releases d,
  vw_me_release_detail e

  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 Received on Thu Aug 08 2002 - 10:38:49 CDT