Re: Join Order Bug?

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 - 06:23:39 CST