Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
![]() |
![]() |
Home -> Community -> Usenet -> c.d.o.misc -> Re: Oracle 9i query results differ from Oracle 8i! Is Oracle 9i reliable when using complex queries with analytic functions? This is very serious question!
Sergey, before I have a look at this further for you, could you ....
A few minutes ago I created the SCOTT schema using the script "utlsampl.sql"
a few minutes ago. I get 4 rows on 10g on a winbox, which you say is wrong.
I get 4 rows using 9.2, same machine. Please confirm you have used the
<b>same</b> SQL script to generate the <b>same</b> SCOTT schema on both your
8i and 9i DB's. This will confirm you are executing your query against the
<b>same</b> data and ruling out the obvious. If the problem persists, we can
look into in more detail.
Regards
Hex
SQL> SELECT
2 dept.dname,
3 rlp_join.rlp_name,
4 rlp_join.s_sal
5 FROM (SELECT
6 dept.dname, 7 rlp.dname rlp_name, 8 rlp.s_sal 9 FROM 10 ( 11 SELECT 12 jn.dname, 13 SUM (emp.sal) s_sal 14 FROM 15 ( 16 SELECT 17 dept.dname, 18 emp.ename, 19 emp.sal sal 20 FROM 21 emp, 22 dept 23 WHERE 24 emp.deptno = dept.deptno) jn, 25 emp 26 WHERE 27 jn.ename = emp.ename 28 GROUP BY ROLLUP (jn.dname) 29 ) rlp, 30 dept 31 WHERE 32 dept.dname = rlp.dname) rlp_join, 33 dept
DNAME RLP_NAME S_SAL
-------------- -------------- ----------
RESEARCH
SALES
ACCOUNTING
OPERATIONS
SQL> SELECT
2 *
3 FROM V$VERSION;
BANNER
SQL> SPOOL OFF
"Sergey Adamenko" <adamenko_usenet_at_ukr.net> wrote in message
news:5c805884.0503040449.71498d40_at_posting.google.com...
> Hi, all!
>
> FOREWORD
> ========
> We develop document management system (in other words, docflow) and
> most of our clients have Oracle 8i. But time is going on and the part
> of clients with Oracle 9i is growing rapidly. So we decided to
> upgrade our primary development and test database to Oracle 9.2.0.5
> EE/SE.
> Recently we discovered that results some of our complex report differ
> from the results in 8i. The report query is rather complicated. It has
> several nested inline views, rollup and many joins.
> It took me some time to create a simplified test for reproducing the
> problem. So here it is.
>
> PROBLEM
> ========
> The query listed below gives different result when executed on Oracle
> 8.1.7.4 EE and Oracle 9.2.0.5 EE. Both are running under MS Windows
> OS.
> Q#1:
>
> SELECT
> dept.dname,
> rlp_join.rlp_name,
> rlp_join.s_sal
> FROM (SELECT
> dept.dname,
> rlp.dname rlp_name,
> rlp.s_sal
> FROM
> (
> SELECT
> jn.dname,
> SUM (emp.sal) s_sal
> FROM
> (
> SELECT
> dept.dname,
> emp.ename,
> emp.sal sal
> FROM
> emp,
> dept
> WHERE
> emp.deptno = dept.deptno) jn,
> emp
> WHERE
> jn.ename = emp.ename
> GROUP BY ROLLUP (jn.dname)
> ) rlp,
> dept
> WHERE
> dept.dname = rlp.dname) rlp_join,
> dept
> WHERE
> dept.dname = rlp_join.dname(+) AND
> rlp_join.dname IS NULL
>
> Oracle 9.2.0.5 EE dives 4 rows.
> Oracle 8.1.7.4 EE dives just one row which is RIGHT!
> Listing of the tests is included at the bottom of this message.
>
> HOW TO REPROCUDE THE PROBLEM
> ================================
> 1. You should have access Oracle 8i and Oracle 9.2.x.x;
> 2. Create a new well-known SCOTT scheme;
> 3. Execute query Q#1 (listed above) on 8i and 9i compare the results.
>
> Here are some additional facts:
> 1. When the keyword "ROLLUP"; is excluded from the query;
> results are the same (1 row);
> 2. When RLP_JOIN inline view is replaced with an equivalent table
> (CTAS for it); results are the same.
>
> I've heard of a problem with merging views in former versions of 9i.
> AFAIK it could lead to an error. But it was fixed in 9.2.0.4, BICBW.
> I have also tried the following:
> 1. Set _COMPLEX_VIEW_MERGING to FALSE;
> 2. Use NO_MERGE hint the query.
> 3. Setting COMPATIBLE.
>
> None of them were helpful. In the 3d case database startup failed
> with
> ORA-00402: database changes by release 9.2.0.0.0 cannot be used by
> release 8.1.7.0.0;
> ORA-00405: compatibility type "Locally Managed SYSTEM tablespace"
>
>
> I'LL BE EXTREMELY GRATEFUL
> ==========================
> 1. if someone will prove or disprove my results (please include
> version and platform information);
> 2. if someone will give a good advice or ever solution to this
> problem.
>
>
> Best regards,
> Sergey Adamenko.
> Senior Oracle DBA.
> Kyiv, Ukraine.
> E-mail: wadamenko_wusenet_at_wukr.net (remove 'w' from E-mail)
>
>
> ==============================================
> TEST LISTING
> [Oracle 9i]
> SQL>
> SQL> SELECT * FROM V$VERSION;
>
> BANNER
> ----------------------------------------------------------------
> Oracle9i Enterprise Edition Release 9.2.0.5.0 - Production
> PL/SQL Release 9.2.0.5.0 - Production
> CORE 9.2.0.6.0 Production
> TNS for 32-bit Windows: Version 9.2.0.5.0 - Production
> NLSRTL Version 9.2.0.5.0 - Production
>
> SQL>
> SQL>
> SQL> SELECT
> 2 dept.dname,
> 3 rlp_join.rlp_name,
> 4 rlp_join.s_sal
> 5 FROM (SELECT
> 6 dept.dname,
> 7 rlp.dname rlp_name,
> 8 rlp.s_sal
> 9 FROM (SELECT
> 10 jn.dname,
> 11 SUM (emp.sal) s_sal
> 12 FROM (SELECT
> 13 dept.dname,
> 14 emp.ename,
> 15 emp.sal sal
> 16 FROM emp,
> 17 dept
> 18 WHERE emp.deptno = dept.deptno) jn,
> 19 emp
> 20 WHERE jn.ename = emp.ename
> 21 GROUP BY ROLLUP (jn.dname)) rlp,
> 22 dept
> 23 WHERE dept.dname = rlp.dname) rlp_join,
> 24 dept
> 25 WHERE dept.dname = rlp_join.dname(+) AND
> 26 rlp_join.dname IS NULL;
>
> DNAME RLP_NAME S_SAL
> -------------- -------------- ----------
> ACCOUNTING
> OPERATIONS
> RESEARCH
> SALES
>
> SQL> EXIT
>
> [Oracle 8i]
> I SQL>
> SQL> SELECT * FROM V$VERSION;
>
> BANNER
> ----------------------------------------------------------------
> Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
> PL/SQL Release 8.1.7.4.0 - Production
> CORE 8.1.7.2.1 Production
> TNS for 32-bit Windows: Version 8.1.7.4.0 - Production
> NLSRTL Version 3.4.1.0.0 - Production
>
> SQL>
> SQL>
> SQL> SELECT
> 2 dept.dname,
> 3 rlp_join.rlp_name,
> 4 rlp_join.s_sal
> 5 FROM (SELECT
> 6 dept.dname,
> 7 rlp.dname rlp_name,
> 8 rlp.s_sal
> 9 FROM (SELECT
> 10 jn.dname,
> 11 SUM (emp.sal) s_sal
> 12 FROM (SELECT
> 13 dept.dname,
> 14 emp.ename,
> 15 emp.sal sal
> 16 FROM emp,
> 17 dept
> 18 WHERE emp.deptno = dept.deptno) jn,
> 19 emp
> 20 WHERE jn.ename = emp.ename
> 21 GROUP BY ROLLUP (jn.dname)) rlp,
> 22 dept
> 23 WHERE dept.dname = rlp.dname) rlp_join,
> 24 dept
> 25 WHERE dept.dname = rlp_join.dname(+) AND
> 26 rlp_join.dname IS NULL;
>
> DNAME RLP_NAME S_SAL
> -------------- -------------- ----------
> OPERATIONS
>
> SQL> exit
Received on Fri Mar 04 2005 - 08:00:36 CST
![]() |
![]() |