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)
Difference in SQL results from Oracle 9i to Oracle 11gR2 [message #487852] |
Sun, 02 January 2011 22:57 ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
![](http://www.orafaq.com/forum/images/custom_avatars/136107.jpg) |
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
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 1064 times)
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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;
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
flyboy
Messages: 1903 Registered: November 2006
|
Senior Member |
|
|
jimit_shaili wrote on Mon, 03 January 2011 09:57Thanks 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:57So 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?
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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 #487918 is a reply to message #487914] |
Mon, 03 January 2011 05:51 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
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 ![Go to previous message Go to previous message](/forum/theme/orafaq/images/up.png) ![Go to next message Go to next message](/forum/theme/orafaq/images/down.png) |
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
|
|
|
|
|
|
Goto Forum:
Current Time: Tue Sep 10 05:46:00 CDT 2024
|