OraFAQ Forum: Server Administration » Difference in SQL results from Oracle 9i to Oracle 11gR2

Home » RDBMS Server » Server Administration » Difference in SQL results from Oracle 9i to Oracle 11gR2 (Oracle 9i/Oracle 11gR2, 9.2.0.1.0/11.2.0.1.0,RHEL 2.1/RHEL 5.0)

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487852]

Sun, 02 January 2011 22:57

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Friends,

I've migrated from Oracle 9i to Oracle 11gR2, when i'm checking my application on Oracle 11gR2, i found that some of the query result are differed from the previous version of Oracle 9i, which is very illogical to me. I've checked the data for all related tables which are involved in one of the query. I've checked all indexes also. Still i'm not convinced that Oracle is doing anything wrong. I cannot recreate the whole scenario here, because of many tables involved in that query. So please suggest me, is it possible that result of queries changed due to difference in versions. Please suggest me also what i have to check. I'm trying to regenerate case so that i can post here.

One of the sample query code.

select 'ProductMaster' producttype,e.schemecd,e.SCHEMESHORTDESC as SCHEMEDESC,to_char(e.schemefrdate,'dd/MM/yyyy')as schemefrdate, to_char(e.schemetodate,'dd/MM/yyyy') as schemetodate,e.mkid ,e.countrycd,e.statecd,e.districtcd,f.schemetype, a.productcd,a.packsizecd,a.packtypecd,a.sortorder1, a.countrycd,a.statecd,a.districtcd,a.p_uniqueid,a.mrp, (a.productcd ||'-'||b.countryname||'-'||c.statename||'-'||d.districtname|| ' -pkt.prc:'||a.pktprice ||' -mrp:'||a.mrp) as pcode 
from  wb.wbproductdetails a 
left join wb.wbcountry b on b.countrycd=a.countrycd  
left join wb.wbstate c on c.countrycd=a.countrycd and  c.countrycd=b.countrycd and c.statecd=a.statecd 
left join sam.samdistrict d on d.countrycd=a.countrycd and  d.countrycd=b.countrycd and  d.countrycd=c.countrycd and d.statecd=a.statecd and  d.statecd=c.statecd and  d.districtcd=a.districtcd 
left join sam.aspschemedetvw 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) 
left join sam.aspschemedet f on f.compcode = e.compcode and f.schemecd = e.schemecd and f.compcode = a.compcode and f.productcd = a.productcd  
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')

Regards

Jimit

[Updated on: Sun, 02 January 2011 23:00]

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487854 is a reply to message #487852]

Sun, 02 January 2011 23:17

BlackSwan
Messages: 26766
Registered: January 2009
Location: SoCal

Senior Member

SELECT 'ProductMaster'                       producttype,
       e.schemecd,
       e.schemeshortdesc                     AS schemedesc,
       To_char(e.schemefrdate, 'dd/MM/yyyy') AS schemefrdate,
       To_char(e.schemetodate, 'dd/MM/yyyy') AS schemetodate,
       e.mkid,
       e.countrycd,
       e.statecd,
       e.districtcd,
       f.schemetype,
       a.productcd,
       a.packsizecd,
       a.packtypecd,
       a.sortorder1,
       a.countrycd,
       a.statecd,
       a.districtcd,
       a.p_uniqueid,
       a.mrp,
       ( a.productcd
         ||'-'
         ||b.countryname
         ||'-'
         ||c.statename
         ||'-'
         ||d.districtname
         || ' -pkt.prc:'
         ||a.pktprice
         ||' -mrp:'
         ||a.mrp )                           AS pcode
FROM   wb.wbproductdetails a
       left join wb.wbcountry b
         ON b.countrycd = a.countrycd
       left join wb.wbstate c
         ON c.countrycd = a.countrycd
            AND c.countrycd = b.countrycd
            AND c.statecd = a.statecd
       left join sam.samdistrict d
         ON d.countrycd = a.countrycd
            AND d.countrycd = b.countrycd
            AND d.countrycd = c.countrycd
            AND d.statecd = a.statecd
            AND d.statecd = c.statecd
            AND d.districtcd = a.districtcd
       left join sam.aspschemedetvw 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 )
       left join sam.aspschemedet f
         ON f.compcode = e.compcode
            AND f.schemecd = e.schemecd
            AND f.compcode = a.compcode
            AND f.productcd = a.productcd
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')

Are statistics the same?

post EXPLAIN PLAN from both systems

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487856 is a reply to message #487854]

Sun, 02 January 2011 23:20

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Quote:

Please suggest me also what i have to check.

Different or wrong results = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487857 is a reply to message #487856]

Sun, 02 January 2011 23:54

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Blackswan

here i'm giving you the explain plan result of both the database. and About statistics, I've gathered statistic of schema level stats in 9i on november,2010 month end and after migrating from 9i to 11g i did not gather the stats on 11g database or even Oracle 9i database. I have migrated my data through export( from lower version) import (into higher version - data only) method. I've created structure on Oracle 11g and then plug in the data via import.

Explain plan from Oracle 9i Database

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------------------------
| Id  | Operation                 |  Name              | Rows  | Bytes | Cost  |
--------------------------------------------------------------------------------
|   0 | SELECT STATEMENT          |                    |     9 |  3843 |   120 |
|*  1 |  HASH JOIN OUTER          |                    |     9 |  3843 |   120 |
|   2 |   VIEW                    |                    |     9 |  3528 |   109 |
|   3 |    NESTED LOOPS OUTER     |                    |     9 |  3276 |   109 |
|   4 |     NESTED LOOPS OUTER    |                    |     9 |  1917 |    19 |
|   5 |      VIEW                 |                    |     9 |  1809 |    19 |
|   6 |       NESTED LOOPS OUTER  |                    |     9 |  1296 |    19 |
|   7 |        VIEW               |                    |     9 |  1224 |    19 |
|   8 |         NESTED LOOPS OUTER|                    |     9 |   522 |    19 |
|*  9 |          TABLE ACCESS FULL| WBPRODUCTDETAILS   |     9 |   486 |    19 |
|* 10 |          INDEX UNIQUE SCAN| PK_COUNTRY         |     1 |     4 |       |
|* 11 |        INDEX UNIQUE SCAN  | PK_STATE           |     1 |     8 |       |
|* 12 |      INDEX UNIQUE SCAN    | PK_DISTRICT        |     1 |    12 |       |
|  13 |     VIEW                  |                    |     1 |   151 |       |
|  14 |      NESTED LOOPS         |                    |     1 |   120 |    10 |
|* 15 |       HASH JOIN           |                    |     1 |    89 |    10 |
|* 16 |        TABLE ACCESS FULL  | ASPSCHMMARKET      |     1 |    41 |     4 |
|* 17 |        TABLE ACCESS FULL  | ASPSCHEMEMASTER    |    15 |   720 |     5 |
|* 18 |       INDEX UNIQUE SCAN   | PK_ASPSCHMPRODDET  |     1 |    31 |       |
|  19 |   TABLE ACCESS FULL       | ASPSCHEMEDET       |  5766 |   197K|    10 |
--------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("F"."PRODUCTCD"(+)="from$_subquery$_009"."PRODUCTCD_5" AND "F".
              "COMPCODE"(+)="from$_subquery$_009"."COMPCODE_0" AND "F"."SCHEM
              ECD"(+)="from$_subquery$_009"."SCHEMECD_43" AND "F"."COMPCODE"(
              +)="from$_subquery$_009"."COMPCODE_42"
   9 - filter("A"."COMPCODE"=3 AND "A"."STARTDATE"<='31-December-2010' AND "A
              "."DISCONTINUEDATE">='31-December-2010' AND ("A"."COUNTRYCD"=1
              OR "A"."COUNTRYCD"=999) AND ("A"."STATECD"=3 OR "A"."STATECD"=9
              99) AND ("A"."DISTRICTCD"=14 OR "A"."DISTRICTCD"=999))
  10 - access("B"."COUNTRYCD"(+)="A"."COUNTRYCD")
  11 - access("C"."COUNTRYCD"(+)="from$_subquery$_003"."COUNTRYCD_2" AND "C".
              "STATECD"(+)="from$_subquery$_003"."STATECD_3"
       filter("C"."COUNTRYCD"(+)="from$_subquery$_003"."COUNTRYCD_23")
  12 - access("D"."COUNTRYCD"(+)="from$_subquery$_005"."COUNTRYCD_2" AND "D".
              "STATECD"(+)="from$_subquery$_005"."STATECD_3" AND "D"."DISTRIC
              TCD"(+)="from$_subquery$_005"."DISTRICTCD_4"
       filter("D"."STATECD"(+)="from$_subquery$_005"."STATECD_29" AND "D"."CO
              UNTRYCD"(+)="from$_subquery$_005"."COUNTRYCD_28" AND "D"."COUNT
              RYCD"(+)="from$_subquery$_005"."COUNTRYCD_23")
  15 - access("A"."COMPCODE"="B"."COMPCODE" AND "A"."SCHEMECD"="B"."SCHEMECD")
  16 - filter(("B"."COUNTRYCD"=1 OR "B"."COUNTRYCD"=999) AND ("B"."STATECD"=3
               OR "B"."STATECD"=999) AND ("B"."DISTRICTCD"=14 OR "B"."DISTRIC
              TCD"=999) AND "B"."SCHEMEFRDATE"<=TO_DATE('31-December-2010','d
              d/mm/yyyy') AND "B"."SCHEMETODATE">=TO_DATE('31-December-2010',
              'dd/mm/yyyy') AND ("B"."PARTYCODE"='999' OR "B"."PARTYCODE"='A0
              101A0199'))
  17 - filter("A"."COMPCODE"="from$_subquery$_005"."COMPCODE_0" AND "A"."USER
              ID"<>'0' AND "A"."USERID"<>'0')
  18 - access("A"."COMPCODE"="C"."COMPCODE" AND "A"."SCHEMECD"="C"."SCHEMECD"
               AND "C"."PRODUCTCD"="from$_subquery$_005"."PRODUCTCD_5"

Note: cpu costing is off

60 rows selected.

Explain plan from Oracle 11g Database

SQL> select * from table(dbms_xplan.display());

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------

----------------------------------------------------------------------------------------------
| Id  | Operation                         | Name                | Rows  | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                     |   992 |   328K|   114   (3)|
|*  1 |  HASH JOIN RIGHT OUTER            |                     |   992 |   328K|   114   (3)|
|*  2 |   TABLE ACCESS FULL               | ASPSCHEMEDET        |  5744 |   196K|    23   (0)|
|   3 |   VIEW                            |                     |   992 |   294K|    90   (3)|
|   4 |    NESTED LOOPS OUTER             |                     |   992 |   246K|    90   (3)|
|   5 |     NESTED LOOPS                  |                     |   992 |   234K|    90   (3)|
|*  6 |      HASH JOIN                    |                     | 15534 |  3200K|    89   (2)|
|   7 |       NESTED LOOPS                |                     |       |       |            |
|   8 |        NESTED LOOPS               |                     |    11 |   979 |    20   (0)|
|*  9 |         TABLE ACCESS FULL         | ASPSCHMMARKET       |    11 |   451 |     9   (0)|
|* 10 |         INDEX UNIQUE SCAN         | PK_ASPSCHMCD_SCHMCD |     1 |       |     0   (0)|
|* 11 |        TABLE ACCESS BY INDEX ROWID| ASPSCHEMEMASTER     |     1 |    48 |     1   (0)|
|  12 |       VIEW                        |                     |  1454 |   173K|    68   (0)|
|  13 |        NESTED LOOPS OUTER         |                     |  1454 |   147K|    68   (0)|
|  14 |         VIEW                      |                     |  1454 |   136K|    68   (0)|
|  15 |          NESTED LOOPS OUTER       |                     |  1454 | 84332 |    68   (0)|
|* 16 |           TABLE ACCESS FULL       | WBPRODUCTDETAILS    |  1454 | 78516 |    68   (0)|
|* 17 |           INDEX UNIQUE SCAN       | PK_COUNTRY          |     1 |     4 |     0   (0)|
|* 18 |         INDEX UNIQUE SCAN         | PK_STATE            |     1 |     8 |     0   (0)|
|* 19 |      INDEX UNIQUE SCAN            | PK_ASPSCHMPRODDET   |     1 |    31 |     0   (0)|
|* 20 |     INDEX UNIQUE SCAN             | PK_DISTRICT         |     1 |    12 |     0   (0)|
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("F"."PRODUCTCD"(+)="A"."PRODUCTCD" AND "F"."COMPCODE"(+)="A"."COMPCODE"
              AND "F"."SCHEMECD"(+)="E"."SCHEMECD" AND "F"."COMPCODE"(+)="E"."COMPCODE")
   2 - filter("F"."COMPCODE"(+)=3)
   6 - access("A"."COMPCODE"="from$_subquery$_005"."COMPCODE")
   9 - filter(("B"."STATECD"=3 OR "B"."STATECD"=999) AND ("B"."PARTYCODE"='999' OR
              "B"."PARTYCODE"='A0101A0199') AND ("B"."DISTRICTCD"=14 OR "B"."DISTRICTCD"=999) AND
              "B"."SCHEMETODATE">=TO_DATE('31-December-2010','dd/mm/yyyy') AND ("B"."COUNTRYCD"=1
              OR "B"."COUNTRYCD"=999) AND "B"."SCHEMEFRDATE"<=TO_DATE('31-December-2010','dd/mm/yyyy
              ') AND "B"."COMPCODE"=3)
  10 - access("A"."COMPCODE"=3 AND "A"."SCHEMECD"="B"."SCHEMECD")
  11 - filter("A"."USERID"<>'0')
  16 - filter("A"."COMPCODE"=3 AND ("A"."STATECD"=3 OR "A"."STATECD"=999) AND
              ("A"."COUNTRYCD"=1 OR "A"."COUNTRYCD"=999) AND ("A"."DISTRICTCD"=14 OR
              "A"."DISTRICTCD"=999) AND "A"."STARTDATE"<='31-December-2010' AND
              "A"."DISCONTINUEDATE">='31-December-2010')
  17 - access("B"."COUNTRYCD"(+)="A"."COUNTRYCD")
       filter("B"."COUNTRYCD"(+)=1 OR "B"."COUNTRYCD"(+)=999)
  18 - access("C"."COUNTRYCD"(+)="A"."COUNTRYCD" AND "C"."STATECD"(+)="A"."STATECD")
       filter(("C"."STATECD"(+)=3 OR "C"."STATECD"(+)=999) AND ("C"."COUNTRYCD"(+)=1
              OR "C"."COUNTRYCD"(+)=999) AND "C"."COUNTRYCD"(+)="B"."COUNTRYCD")
  19 - access("C"."COMPCODE"=3 AND "A"."SCHEMECD"="C"."SCHEMECD" AND
              "C"."PRODUCTCD"="from$_subquery$_005"."PRODUCTCD")
  20 - access("D"."COUNTRYCD"(+)="A"."COUNTRYCD" AND "D"."STATECD"(+)="A"."STATECD"
              AND "D"."DISTRICTCD"(+)="A"."DISTRICTCD")
       filter(("D"."STATECD"(+)=3 OR "D"."STATECD"(+)=999) AND
              ("D"."DISTRICTCD"(+)=14 OR "D"."DISTRICTCD"(+)=999) AND ("D"."COUNTRYCD"(+)=1 OR
              "D"."COUNTRYCD"(+)=999) AND "D"."STATECD"(+)="C"."STATECD" AND
              "D"."COUNTRYCD"(+)="C"."COUNTRYCD" AND "D"."COUNTRYCD"(+)="B"."COUNTRYCD")

Note
-----
   - 'PLAN_TABLE' is old version

62 rows selected.

Regards

Jimit

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487867 is a reply to message #487857]

Mon, 03 January 2011 00:49

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Blackswan

As i think statistic can have impact on execution plan or speed of the result but it should not impact the result set of query.

Regards

Jimit

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487871 is a reply to message #487867]

Mon, 03 January 2011 01:05

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Blackswan

As a try i've deleted the stats of relative schema on both the database and regenerate stats for those schema on both database. But still there is a difference between result set of query. So stats might not be the actual issue.
What can i do next other than contact oracle support. I don't have access to oracle support or metalink.Please suggest where do i search more. Have you ever come across such type of problem before? Is it regular or known issue of version migration in Oracle?

SQL> execute DBMS_STATS.DELETE_SCHEMA_STATS (OWNNAME => 'SAM');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_schema_stats(OWNNAME => 'SAM', OPTIONS => 'GATHER AUTO');

PL/SQL procedure successfully completed.

SQL>  execute DBMS_STATS.DELETE_SCHEMA_STATS (OWNNAME => 'WB');

PL/SQL procedure successfully completed.

SQL> execute dbms_stats.gather_schema_stats(OWNNAME => 'WB', OPTIONS => 'GATHER AUTO');

PL/SQL procedure successfully completed.

Regards

Jimit

[Updated on: Mon, 03 January 2011 01:08]

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487878 is a reply to message #487871]

Mon, 03 January 2011 01:52

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Friends

I've created a test case which gives a different result in Oracle 9i and Oracle 11gR2. Just look into this matter and guide me what can be done.Please check attachment for sql file.

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
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPB
(
  COUNTRYCD  NUMBER(4)
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPC
(
  COUNTRYCD  NUMBER(4),
  STATECD    NUMBER(4)
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPD
(
  COUNTRYCD   NUMBER(4),
  STATECD     NUMBER(4),
  DISTRICTCD  NUMBER(4)
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


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),
  USERID           VARCHAR2(30 BYTE)            NOT NULL,
  SCHEMEFRDATE     DATE                         NOT NULL,
  SCHEMETODATE     DATE                         NOT NULL,
  SCHEMESHORTDESC  VARCHAR2(30 BYTE)            NOT NULL
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;


CREATE TABLE TEMPF
(
  COMPCODE    NUMBER(4)                         NOT NULL,
  PRODUCTCD   VARCHAR2(50 BYTE)                 NOT NULL,
  SCHEMETYPE  VARCHAR2(5 BYTE)                  NOT NULL,
  SCHEMECD    NUMBER(8)                         NOT NULL
)
LOGGING 
NOCACHE
NOPARALLEL
NOMONITORING;



SET DEFINE OFF;
Insert into TEMPA
   (P_UNIQUEID, COMPCODE, COUNTRYCD, STATECD, DISTRICTCD, 
    PRODUCTCD, STARTDATE, DISCONTINUEDATE)
 Values
   (5233, 3, 1, 3, 999, 
    'WB-BROKENS-500 GMS-POUCH', 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, 
    'WB-BROKENS-1 KGS-POUCH', 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;

SET DEFINE OFF;
Insert into TEMPB
   (COUNTRYCD)
 Values
   (1);
Insert into TEMPB
   (COUNTRYCD)
 Values
   (999);
COMMIT;

SET DEFINE OFF;
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (1, 3);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (1, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (2, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (3, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (4, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (5, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (6, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (7, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (8, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (9, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (10, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (11, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (12, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (13, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (14, 999);
Insert into TEMPC
   (COUNTRYCD, STATECD)
 Values
   (999, 999);
COMMIT;

SET DEFINE OFF;
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 14, 22);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 14, 23);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 14, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (1, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (2, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (3, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (4, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (5, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (6, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (7, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (8, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (9, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (10, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (11, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (12, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (13, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (14, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (15, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (16, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (17, 999, 999);
Insert into TEMPD
   (COUNTRYCD, STATECD, DISTRICTCD)
 Values
   (999, 999, 999);
COMMIT;

SET DEFINE OFF;
Insert into TEMPE
   (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD, 
    SCHEMECD, PARTYCODE, USERID, SCHEMEFRDATE, SCHEMETODATE, 
    SCHEMESHORTDESC)
 Values
   (3, 'WB-BROKENS-500 GMS-POUCH', 1, 14, 999, 
    1554, '999', 'chintan.patel', 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'), 
    '50gmWBMasalaOnWBLeaf500gm');
Insert into TEMPE
   (COMPCODE, PRODUCTCD, COUNTRYCD, STATECD, DISTRICTCD, 
    SCHEMECD, PARTYCODE, USERID, SCHEMEFRDATE, SCHEMETODATE, 
    SCHEMESHORTDESC)
 Values
   (3, 'WB-BROKENS-500 GMS-POUCH', 1, 3, 999, 
    1554, '999', 'chintan.patel', 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'), 
    '50gmWBMasalaOnWBLeaf500gm');
COMMIT;

SET DEFINE OFF;
Insert into TEMPF
   (COMPCODE, PRODUCTCD, SCHEMETYPE, SCHEMECD)
 Values
   (3, 'WB-BROKENS-500 GMS-POUCH', 'DIFWP', 1554);
COMMIT;

Result on Oracle 9i database

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  left join tempb b on b.countrycd=a.countrycd  
  4  left join tempc c on c.countrycd=a.countrycd and  c.countrycd=b.countrycd and c.statecd=a.statecd 
  5  left join tempd d on d.countrycd=a.countrycd and  d.countrycd=b.countrycd and  d.countrycd=c.countrycd and d.statecd=a.statecd and  d.statecd=c.statecd and  d.districtcd=a.districtcd
  6  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) 
  7  left join tempf f on f.compcode = e.compcode and f.schemecd = e.schemecd and f.compcode = a.compcode and f.productcd = a.productcd  
  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 and 
 14  e.partycode in('A0101A0199','999') and 
 15  e.userid<>'0' and  
 16  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 50gmWBMasalaOnWBLeaf500gm
      5234

Result on Oracle 11gR2 database

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  left join tempb b on b.countrycd=a.countrycd  
  4  left join tempc c on c.countrycd=a.countrycd and  c.countrycd=b.countrycd and c.statecd=a.statecd 
  5  left join tempd d on d.countrycd=a.countrycd and  d.countrycd=b.countrycd and  d.countrycd=c.countrycd and d.statecd=a.statecd and  d.statecd=c.statecd and  d.districtcd=a.districtcd
  6  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) 
  7  left join tempf f on f.compcode = e.compcode and f.schemecd = e.schemecd and f.compcode = a.compcode and f.productcd = a.productcd  
  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 and 
 14  e.partycode in('A0101A0199','999') and 
 15  e.userid<>'0' and  
 16  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 50gmWBMasalaOnWBLeaf500gm

Regards

Jimit

Attachment: temp.sql
(Size: 6.15KB, Downloaded 1089 times)

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487881 is a reply to message #487878]

Mon, 03 January 2011 02:45

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Thank you for the testcase. Just two comments to it:
1) it is unnecessarily complex - only tables TEMPA and TEMPE are used for generating the result set (although I cannot confirm that after removal of all tables from the query except these two the same resultset would be generated in Oracle 9i)
2) when converting string literal to date in the query, could you use to_date with the same format as the string literal? It really hurts to see dates like this:

'31-December-2010'
to_date('31-December-2010','dd/mm/yyyy')

To the question, it depends whether you want the same results in 11g or if you want correct results. It looks that 9i treated filter conditions for TEMPE (WHERE clause) as join conditions (ON clause). If you want the same results in 11g, move them. After removal of unnecessary tables, the query would look like this:

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) 
    and e.partycode in('A0101A0199','999') and e.userid<>'0'
    and e.schemefrdate<=to_date('31/12/2010','dd/mm/yyyy')
    and e.schemetodate>=to_date('31/12/2010','dd/mm/yyyy')
where  
  a.compcode=3 and  
  a.countrycd in(1,999) and 
  a.statecd in(3,999) and 
  a.districtcd in(14,999) and 
  to_date( '31/12/2010','dd/mm/yyyy') between a.startdate and a.discontinuedate;

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487882 is a reply to message #487881]

Mon, 03 January 2011 02:57

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Flyboy,

Thanks for giving your valuable time, but to your comments i would like to say that there are many more column in select clause but for the ease and effectiveness of problem i just remark them.Therefore the tables which are in relation is required for perfect result. And it was my bad luck that my application was developed by third party, and i don't have the actual source code of it. I captured this query from my application log. Therefor i can't change anything in query. I do change in server parameter or configuration. So if i want same result in Oracle 11g as per the Oracle 9i what can i do other than changing a query. Give your suggestions.

Regards

Jimit

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487887 is a reply to message #487882]

Mon, 03 January 2011 03:27

flyboy
Messages: 1903
Registered: November 2006

Senior Member

jimit_shaili wrote on Mon, 03 January 2011 09:57

Thanks for giving your valuable time, but to your comments i would like to say that there are many more column in select clause but for the ease and effectiveness of problem i just remark them.Therefore the tables which are in relation is required for perfect result.

That is the aim of test case which you provided in your last post - reproduce the real problem using the least possible objects. Can you confirm, that after removal of tables TEMPB, TEMPC, TEMPD and TEMPF from FROM clause returns the same results in 9i?
jimit_shaili wrote on Mon, 03 January 2011 09:57

So if i want same result in Oracle 11g as per the Oracle 9i what can i do other than changing a query. Give your suggestions.

The results are correct for given query in 11g. If you want different results I would opt for changing the query. What about requiring third party for fixing/upgrading the application?
You might play with OPTIMIZER_FEATURES_ENABLE parameter (http://download.oracle.com/docs/cd/E11882_01/server.112/e17110/initparams164.htm#CHDFABEF), however I would not go this way as it may seriously affect other parts of application (in fact, I have never done this nastiness on production). In that case, why would you upgrade to 11g and would not stay at 9i?

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487888 is a reply to message #487887]

Mon, 03 January 2011 03:44

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Flyboy

As per your suggestion i removed the tables which infact not needed in test case. i removed them but still my problem is not solved. I'd tried earlier with change parameter values of till
OPTIMIZER_FEATURES_ENABLE='9.2.0', but it is not working. Infact by changing the value query hangs for hours.

Result in Oracle 9i database

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  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)
  4  where  
  5  a.compcode=3 and  
  6  a.countrycd in(1,999) and 
  7  a.statecd in(3,999) and 
  8  a.districtcd in(14,999) and 
  9  '31-December-2010' between a.startdate and a.discontinuedate and 
 10  e.partycode in('A0101A0199','999') and 
 11  e.userid<>'0' and  
 12  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 50gmWBMasalaOnWBLeaf500gm
      5234

Result in Oracle 11g database

SQL> select a.p_uniqueid,e.schemeshortdesc
  2  from  tempa a 
  3  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) 
  4  where  
  5  a.compcode=3 and  
  6  a.countrycd in(1,999) and 
  7  a.statecd in(3,999) and 
  8  a.districtcd in(14,999) and 
  9  '31-December-2010' between a.startdate and a.discontinuedate and 
 10  e.partycode in('A0101A0199','999') and 
 11  e.userid<>'0' and  
 12  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 50gmWBMasalaOnWBLeaf500gm

Regards

Jimit

[Updated on: Mon, 03 January 2011 03:45]

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487891 is a reply to message #487888]

Mon, 03 January 2011 04:14

Michel Cadot
Messages: 68718
Registered: March 2007
Location: Saint-Maur, France, https...

Senior Member
Account Moderator

Michel Cadot wrote on Mon, 03 January 2011 06:20

Quote:
Please suggest me also what i have to check.

Different or wrong results = Oracle bug => search on Metalink and/or call Oracle support

Regards
Michel

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487914 is a reply to message #487891]

Mon, 03 January 2011 05:26

flyboy
Messages: 1903
Registered: November 2006

Senior Member

And, as Oracle gives correct result for that query in 11g, for obtaining the same (wrong) results as in 9i, I would either stay on 9i with that application or require its fix from third party. I am afraid there is no third way.

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487918 is a reply to message #487914]

Mon, 03 January 2011 05:51

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Dear Flyboy

Thanks for your help, but how can you say that why should i've to move towards 11gR2. There are so many things which i can't discuss over this forum or let you know,there are much more and improved feature than oracle 9i and it is totally my company's decision to move on newer version of Oracle. And how could you say that result is wrong in Oracle 9i and if it is wrong then why oracle itself not fix it. It is just one the problem that i came to know, there may be lots of other one also. Do you think any software developer develop there queries or application on version basis. If that's the likely case then you should be in heaven. Anyway thanks for your help.Be a part of this forum for so many long time, i think much more help come from gurus as earlier.

Thanks

Regards

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487924 is a reply to message #487918]

Mon, 03 January 2011 06:34

flyboy
Messages: 1903
Registered: November 2006

Senior Member

You asked for my suggestions, so I posted them as these are the only ones I can come with. Oracle apparently fixed this bug, but probably not in 9i (at least not in the version you were using). Each software has bugs, Oracle (perhaps with that third party application) are not exceptions from this rule. It is unpleasant, but you really should become accustomed to live with this (of course after excluding other possible reasons for the unexpected behaviour). As each new Oracle version brings new features, the developer should develop the query based on actual Oracle version and check it when upgrading to the newer one.

If you do not agree with me and think that 9i returned correct result, you may state reason why you think so (as I did).

You may try to contact Oracle support (as suggested by Michel), but I would not put much expectation to this. This is the best I can tell. This is just a public forum from volunteers; I have no idea what did you expect from it but (at least I) have less influence on Oracle software than is your impact on that third party application vendor.

[Edit: Added the last sentence in the last paragraph]

[Updated on: Mon, 03 January 2011 06:43]

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487927 is a reply to message #487924]

Mon, 03 January 2011 06:48

jimit_shaili
Messages: 237
Registered: June 2006
Location: India, Ahmedabad

Senior Member

Thanks Flyboy,

I totally agree with you, it was my frustration. No regrets to you. I will have to contact oracle support or convince my application developers to change the queries.

Regards

Jimit

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487934 is a reply to message #487918]

Mon, 03 January 2011 09:19

Mahesh Rajendran
Messages: 10708
Registered: March 2002
Location: oracleDocoVille

Senior Member
Account Moderator

Quote:

Do you think any software developer develop there queries or application on version basis.

And Oracle expects you to properly UPGRADE to a higher version.
As already stated in your other thread, export/import will not always work. It is just a logical method to move data around.
And all the suggestions here is based on fact, you did atleast copy the properly. No proof for that yet Smile

Smile

.

Report message to a moderator

Re: Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #488169 is a reply to message #487924]

Wed, 05 January 2011 07:07

flyboy
Messages: 1903
Registered: November 2006

Senior Member

Just curious whether also others feel the same difference between JOIN and filter (WHERE) conditions and its wrong implementation in 9i (as Oracle outer join syntax does not distinguish them).

So, here is a link to OTN thread about the same issue, as it seems to be more live than this one (and as I am really curious whether it is possible to configure Oracle to return wrong query result): http://forums.oracle.com/forums/thread.jspa?threadID=2152653&tstart=0

Report message to a moderator

Previous Topic:	Alter Tablespace
Next Topic:	Database Resident Connection Pooling

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Sun Dec 01 13:11:18 CST 2024