Oracle interprets ANSI Inner Join as an Outer Join?

From: Charles Schultz <sacrophyte_at_gmail.com>
Date: Wed, 23 Sep 2009 14:24:07 -0500
Message-ID: <7b8774110909231224s2d965396y70bcd3a773e6e12e_at_mail.gmail.com>

Good day, list,
Not sure if this is an Oracle bug or a feature; under OSEE 10.2.0.2 on RHEL4 I have an ANSI "LEFT JOIN" that Oracle is turning into an OUTER JOIN. Any idea why? I am having a hard time getting the optimizer to force a INNER join without changing the query (stinking db-agnostic vendors *grin*).

NOTE: I do not know ANSI SQL all that well, but from what I have read, INNER joins are implied unless OUTER joins are explicitly stated.

SELECT ca_contact.last_name,

 ca_contact.first_name,
 ca_contact.middle_name,
 ca_contact.pri_phone_number,
 ca_contact.alternate_identifier,
 ca_contact.organization_uuid,
 ca_contact.location_uuid,
 ca_contact.contact_uuid

FROM (MDBADMIN.ca_contact LEFT JOIN MDBADMIN.usp_contact ON ca_contact.contact_uuid = usp_contact.contact_uuid) WHERE usp_contact.z_uin = 'some_number' OR ca_contact.userid = 'some_value' ORDER BY ca_contact.last_name , ca_contact.first_name , ca_contact.middle_name
/

---

| Id  | Operation                     | Name              | E-Rows |
--------------------------------------------------------------------
|*  1 |  FILTER                       |                   |        |


|   2 |   NESTED LOOPS OUTER          |                   |     10 |

|   3 |    TABLE ACCESS BY INDEX ROWID| CA_CONTACT        |    154K|

|   4 |     INDEX FULL SCAN           | CA_CONTACT_IDX_02 |     10 |

|   5 |    TABLE ACCESS BY INDEX ROWID| USP_CONTACT       |      1 |

|*  6 |     INDEX UNIQUE SCAN         | XPKUSP_CONTACT    |      1 |
--------------------------------------------------------------------

Predicate Information (identified by operation id):

---

   1 - filter(("USP_CONTACT"."Z_UIN"=SYS_OP_C2C(:SYS_B_0) OR
              "CA_CONTACT"."USERID"=SYS_OP_C2C(:SYS_B_1)))
   6 - access("CA_CONTACT"."CONTACT_UUID"="USP_CONTACT"."CONTACT_UUID")

-- 
Charles Schultz

--
http://www.freelists.org/webpage/oracle-l

Received on Wed Sep 23 2009 - 14:24:07 CDT