Oracle interprets ANSI Inner Join as an Outer Join?
- From: Charles Schultz <sacrophyte@xxxxxxxxx>
- To: ORACLE-L <oracle-l@xxxxxxxxxxxxx>
- Date: Wed, 23 Sep 2009 14:24:07 -0500
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
Other related posts:
