yes, you are right... kind of obvious... I guess I was staring at it
for too long. Thanks!
dudester <duderino_at_zdnetmail.com> wrote in message news:<3D529EB2.8040103_at_zdnetmail.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
Received on Fri Aug 09 2002 - 09:11:53 CDT
