Re: Join Order Bug?

From: Buck Turgidson <jc_va_at_hotmail.com>
Date: Mon, 25 Feb 2002 21:27:55 +0000 (UTC)
Message-ID: <4429d30e39a3409ba9c03708a5864eaf.38849@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 Mon Feb 25 2002 - 15:27:55 CST