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
Received on Thu Aug 08 2002 - 11:39:14 CDT
