Left outer Join converted into Inner Join Part-2

Here is the detailed description of problem mentioned here.

CREATE TABLE TEMPA
(
P_UNIQUEID NUMBER(8),
COMPCODE NUMBER(4),
COUNTRYCD NUMBER(4),
STATECD NUMBER(4),
DISTRICTCD NUMBER(4),
PRODUCTCD VARCHAR2(50 BYTE),
STARTDATE DATE,
DISCONTINUEDATE DATE
)

CREATE TABLE TEMPE
(
COMPCODE NUMBER(4) NOT NULL,
PRODUCTCD VARCHAR2(50 BYTE) NOT NULL,
COUNTRYCD NUMBER(4),
STATECD NUMBER(4),
DISTRICTCD NUMBER(4),
SCHEMECD NUMBER(8) NOT NULL,
PARTYCODE VARCHAR2(10 BYTE),
SCHEMEFRDATE DATE NOT NULL,
SCHEMETODATE DATE NOT NULL,
SCHEMESHORTDESC VARCHAR2(30 BYTE) NOT NULL
)

Insert into TEMPA (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD,PRODUCTCD, STARTDATE, DISCONTINUEDATE)
Values(5233, 3, 1, 3, 999, 'Product-1', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));

Insert into TEMPA (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD,PRODUCTCD, STARTDATE, DISCONTINUEDATE)
Values(5234, 3, 1, 3, 999, 'Product-2', TO_DATE('10/11/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'),
TO_DATE('12/31/9999 00:00:00', 'MM/DD/YYYY HH24:MI:SS'));
COMMIT;

Insert into TEMPE (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD,SCHEMECD, PARTYCODE, SCHEMEFRDATE, SCHEMETODATE, SCHEMESHORTDESC)
Values(3, 'Product-1', 1, 14, 999,1554, '999', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'LeftToInner');

Insert into TEMPE (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD, SCHEMECD, PARTYCODE,SCHEMEFRDATE, SCHEMETODATE, SCHEMESHORTDESC)
Values(3, 'Product-1', 1, 3, 999, 1554, '999', TO_DATE('12/25/2010 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), TO_DATE('01/07/2011 00:00:00', 'MM/DD/YYYY HH24:MI:SS'), 'LeftToInner');

Commit;

Now lets check result of our sql statement in both 9i and 10g database.

Result of query on Oracle 9i Database (Oracle version 9.2.0.1.0, RHEL 2.1)

Select a.p_uniqueid,e.schemeshortdesc
from tempa a
left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd
and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
where
a.compcode=3 and
a.countrycd in(1,999) and
a.statecd in(3,999) and
a.districtcd in(14,999) and
'31-December-2010' between a.startdate and a.discontinuedate and
e.partycode in('A0101A0199','999') and
e.userid<>'0' and
e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
/

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner
5234


Result of query on Oracle 10gR2 Database

Select a.p_uniqueid,e.schemeshortdesc
from tempa a
left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
where
a.compcode=3 and
a.countrycd in(1,999) and
a.statecd in(3,999) and
a.districtcd in(14,999) and
'31-December-2010' between a.startdate and a.discontinuedate and
e.partycode in('A0101A0199','999') and
e.userid<>'0' and
e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
/

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner


As you notice we have different result set for Oracle 9i and Oracle 10g(For Oracle 11g as well). The reason is in Oracle 9i we execute left outer join query(At least we believe so!!) and Oracle execute it as left outer join operation. While in Oracle 10g, same sql statement executed as inner join operation.

To confirm inner join here is execution plan for query:-

Execution Plan
----------------------------------------------------------
Plan hash value: 3150946153

----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 248 | 7 (15)| 00:00:01 |
|* 1 | HASH JOIN | | 1 | 248 | 7 (15)| 00:00:01 |
|* 2 | TABLE ACCESS FULL| TEMPE | 1 | 138 | 3 (0)| 00:00:01 |
|* 3 | TABLE ACCESS FULL| TEMPA | 2 | 220 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------

The problem is the Sql is not written properly for left outer join operation. If you move some part of where clause up with ON clause then Oracle will perform Left outer join. See this:-

SQL> ed
Wrote file afiedt.buf
1 select a.p_uniqueid,e.schemeshortdesc,e.userid,e.partycode,e.schemefrdate,e.schemetodate
2 from tempa a
3 left join tempe e on e.compcode = a.compcode and e.productcd = a.productcd
4 and e.countrycd in(1,999) and e.statecd in(3,999) and e.districtcd in(14,999)
5 and e.partycode in('A0101A0199','999') and
6 e.userid<>'0' and e.schemefrdate<=to_date('31-December-2010','dd/mm/yyyy') and 7 e.schemetodate>=to_date('31-December-2010','dd/mm/yyyy')
8 where
9 a.compcode=3 and
10 a.countrycd in(1,999) and
11 a.statecd in(3,999) and
12 a.districtcd in(14,999) and
13* '31-December-2010' between a.startdate and a.discontinuedate
14 /

P_UNIQUEID SCHEMESHORTDESC
---------- ------------------------------
5233 LeftToInner
5234

Execution Plan
----------------------------------------------------------
Plan hash value: 965687122

-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 232 | 9 (0)| 00:00:01 |
| 1 | NESTED LOOPS OUTER | | 2 | 232 | 9 (0)| 00:00:01 |
|* 2 | TABLE ACCESS FULL | TEMPA | 2 | 114 | 3 (0)| 00:00:01 |
| 3 | VIEW | | 1 | 59 | 3 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL| TEMPE | 1 | 98 | 3 (0)| 00:00:01 |
-----------------------------------------------------------------------------