Re: Join Order Bug?

OP's version is 8.1.6.0.0. No platform stated, AFAIK. I'm intrigued to read the outcome of this one...

Paul

"Andre van Winssen (andrew)" <andrew-no-spam-svp_at_info.nl> wrote in message news:3c7b7e58$0$230$4d4ebb8e_at_news.nl.uu.net...
> Yes, you must be running into a bug of the rule based optmizer.
> What RDBMS version are you running on? I guess something like 8.0.3
> > I could understand that they could relate to performance issues, but I
> > didn't think they would determine result sets.
> Unfortunately this is what happened with older versions of the
> cost based optmizer. I'm surprised to see this happening with the
> 19-rule based optimizer.
> If it is an older version I'd advice to upgrade to 817 or 9i.
>
> Andre van Winssen
>
>
> "Buck Turgidson" <jc_va_at_hotmail.com> wrote in message
> news:4429d30e39a3409ba9c03708a5864eaf.38849_at_mygate.mailgate.org...
> > I wish it were an exam question, and not real life. Why would indexes
> > and explain plans make a difference on what rows a query returns? I
> > could understand that they could relate to performance issues, but I
> > didn't think they would determine result sets.
> >
> > The 2 queries have the same explain plan, BTW.
> >
> >
> > SQL> SELECT COUNT(*)
> > 2 FROM PS_YE_DATA E ,PS_W2_COMPANY W
> > 3 WHERE E.COMPANY = '500'
> > 4 AND W.CALENDAR_YEAR = E.CALENDAR_YEAR
> > 5 AND W.CALENDAR_YEAR = 2001
> > 6 AND E.TAXFORM_ID = 'W'
> > 7 AND W.COMPANY = E.COMPANY;
> >
> > COUNT(*)
> > ----------
> > 0
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=RULE
> > 1 0 SORT (AGGREGATE)
> > 2 1 NESTED LOOPS
> > 3 2 INDEX (RANGE SCAN) OF 'PS_YE_DATA' (UNIQUE)
> > 4 2 INDEX (RANGE SCAN) OF 'PS_W2_COMPANY' (UNIQUE)
> >
> >
> >
> > SQL>
> > SQL>
> > SQL>
> > SQL> SELECT COUNT(*)
> > 2 FROM PS_YE_DATA E ,PS_W2_COMPANY W
> > 3 WHERE E.COMPANY = '500'
> > 4 AND W.CALENDAR_YEAR = 2001
> > 5 AND W.CALENDAR_YEAR = E.CALENDAR_YEAR
> > 6 AND E.TAXFORM_ID = 'W'
> > 7 AND W.COMPANY = E.COMPANY;
> >
> > COUNT(*)
> > ----------
> > 4779
> >
> > Execution Plan
> > ----------------------------------------------------------
> > 0 SELECT STATEMENT Optimizer=RULE
> > 1 0 SORT (AGGREGATE)
> > 2 1 NESTED LOOPS
> > 3 2 INDEX (RANGE SCAN) OF 'PS_YE_DATA' (UNIQUE)
> > 4 2 INDEX (RANGE SCAN) OF 'PS_W2_COMPANY' (UNIQUE)
> >
> >
> >
> > --
> > Posted via Mailgate.ORG Server - http://www.Mailgate.ORG
>
>
Received on Tue Feb 26 2002 - 13:23:42 CST