Home » SQL & PL/SQL » SQL & PL/SQL » Same query returns differenent result sets in oracle 9i and 10g (oracle db,9.2,10.2,xp)
icon1.gif  Same query returns differenent result sets in oracle 9i and 10g [message #426758] Mon, 19 October 2009 07:30 Go to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Could anyone please clarify the issue i am having on a query. I have query that is returning around 2000 records in oracle db 9.2.0.8.0, at the same time with same data if run on 10.2.0.1.0 is returning only 12 records.
what might be the reason for this descrepancy, is something in 9i is considered differently in 10g?

Here is the query

SELECT 0
      ,ot_req_head.rh_sys_id
      ,ot_req_head.rh_comp_code
      ,ot_req_head.rh_txn_code
      ,ot_req_head.rh_no
      ,ot_req_head.rh_appr_uid
      ,'Action to be taken' action
      ,ot_req_head.rh_appr_dt
      ,NULL AUTH_TO_UID
      ,NULL
      ,NULL
      ,NULL from_remarks
      ,NULL
      ,NULL
      ,'REQ'
      ,ot_req_head.rh_amd_no 
      ,ot_req_head.rh_amd_dt
      ,ot_req_head.rh_cr_uid 
      ,DECODE(ot_req_head.rh_charge_area_num,6,ot_req_head.rh_locn_code,1,ot_req_head.rh_charge_code,NULL)
      ,ot_req_head.rh_cr_dt 
      ,NULL
FROM   orioni.om_txn_setup,
       orioni.ot_req_head,
       iscoit.isco_txn_auth,
       (SELECT DISTINCT rh_sys_id
        FROM   orioni.pending_mr) pending_mr
WHERE  ot_req_head.rh_txn_code=txns_txn_code
AND    auth_comp_code(+)=ot_req_head.rh_comp_code
AND    auth_action_to_type(+) IS NULL
AND    auth_head_sys_id(+)=ot_req_head.rh_sys_id
AND    auth_txn_code(+)=ot_req_head.rh_txn_code
AND    ot_req_head.rh_clo_status IS NULL
AND    ot_req_head.rh_comp_code='001'
AND    txns_txnp_code='PURREQ'
AND    txns_values='N'
AND    ot_req_head.rh_appr_status=3
AND    ot_req_head.rh_sys_id = pending_mr.rh_sys_id 
AND NOT EXISTS (SELECT 'X'
                FROM   iscoit.isco_txn_auth A
                WHERE  a.auth_head_sys_id = isco_txn_auth.auth_head_sys_id
                AND    a.auth_action_from_uid = ot_req_head.rh_appr_uid
                AND    a.auth_action_from_type = 'Action to be taken'
                AND    a.auth_action_from_dt = ot_req_head.rh_appr_dt
                AND    NVL(a.auth_txn_amend_no,0) = NVL(ot_req_head.rh_amd_no,0)
                AND    a.auth_action_to_type = 'REJECT')
AND     ot_req_head.rh_cr_dt > TO_DATE('16-MAR-2008','DD-MON-YYYY')


{reformat code into something slightly less space consuming}

[Updated on: Mon, 19 October 2009 08:21] by Moderator

Report message to a moderator

Re: Same query returns differenent result sets in oracle 9i and 10g [message #426760 is a reply to message #426758] Mon, 19 October 2009 07:42 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you absolutely certain the data is the same in both db's?
Cause that's quite a large discrepancy.

Can't see anything there that could possibly cause it, though I have wonder why have you got an outer join - you're only selecting from 1 table.

Think you're going to have to debug this the hard way.
Remove everything from the query that references the tables other than ot_req_head and run it on both dbs.
Check you get the same number of records.
Re-add the other tables one at a time, constantly re-running on both dbs.
When you get a different number of rows you'll know that's the problem bit.
If the solution still isn't obvious then post back what you've found and we'll see if we can work out what's going on.
Re: Same query returns differenent result sets in oracle 9i and 10g [message #426762 is a reply to message #426758] Mon, 19 October 2009 07:49 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Hi ,

Here's the similar script, i have icncluded the query that i fired against both 9i and 10g.

CREATE TABLE T_1(VAL1 NUMBER,VAL2 VARCHAR2(10));

CREATE TABLE T_2(VAL1 NUMBER,VAL2 VARCHAR2(10));

INSERT INTO T_1 VALUES(1,'FIRST');
INSERT INTO T_1 VALUES(2,'SECOND');
INSERT INTO T_2 VALUES(1,'FIRST');
INSERT INTO T_2 VALUES(2,'SECOND');
INSERT INTO T_1 VALUES(3,'THIRD');
INSERT INTO T_1 VALUES(4,'FOURTH');
INSERT INTO T_1 VALUES(5,'FIFTH');
COMMIT;

Query on 10g

SELECT A.VAL1,B.VAL1 FROM
T_1 A,T_2 B
WHERE A.VAL1=B.VAL1;

VAL1 VAL1
-----------------------------------------------------------------
1 1 
2 2 
SELECT A.VAL1,B.VAL1 FROM
T_1 A,T_2 B
WHERE A.VAL1=B.VAL1(+)


VAL1 VAL1
-----------------------------------------------------------------
1 1 
2 2 
5 
4 
3 

SELECT A.VAL1,B.VAL1 FROM
T_1 A,T_2 B
WHERE A.VAL1=B.VAL1(+)
AND
NOT EXISTS (SELECT 'X' FROM t_2 C WHERE c.val1=b.val1)

no rows selected

[b]Query on 9i[/b]
SELECT A.VAL1,B.VAL1 FROM
T_1 A,T_2 B
WHERE A.VAL1=B.VAL1;

VAL1 VAL1


-----------------------------------------------------------------
1 1 
2 2 

SELECT A.VAL1,B.VAL1 FROM
T_1 A,T_2 B
WHERE A.VAL1=B.VAL1(+);

VAL1 VAL1
----------------------------------------------------------------

1 1 
2 2 
3 
5 

SELECT A.VAL1,B.VAL1 FROM
T_1 A,T_2 B
WHERE A.VAL1=B.VAL1(+)
AND
NOT EXISTS (SELECT 'X' FROM t_2 C WHERE c.val1=b.val1);

VAL1 VAL1
--------------------------------------------------------------------------------
 --------- 
3 
5 


{changed Quote tags to Code tags}

[Updated on: Mon, 19 October 2009 08:23] by Moderator

Report message to a moderator

Re: Same query returns differenent result sets in oracle 9i and 10g [message #426763 is a reply to message #426758] Mon, 19 October 2009 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Thanks for the example, but next time can you please use code tags rather than quote tags - see the orafaq forum guide if you're not sure how.

You appear to have buggy DBs - both of them.
When I run your example on 10.2.0.2 I get the following:

Connected to:
Oracle Database 10g Release 10.2.0.2.0 - 64bit Production

SQL> SELECT A.VAL1,B.VAL1 FROM
  2  T_1 A,T_2 B
  3  WHERE A.VAL1=B.VAL1;

      VAL1       VAL1
---------- ----------
         1          1
         2          2

SQL> SELECT A.VAL1,B.VAL1 FROM
  2  T_1 A,T_2 B
  3  WHERE A.VAL1=B.VAL1(+);

      VAL1       VAL1
---------- ----------
         1          1
         2          2
         5
         4
         3

SQL> SELECT A.VAL1,B.VAL1 FROM
  2  T_1 A,T_2 B
  3  WHERE A.VAL1=B.VAL1(+)
  4  AND
  5  NOT EXISTS (SELECT 'X' FROM t_2 C WHERE c.val1=b.val1);

      VAL1       VAL1
---------- ----------
         3
         4
         5


Which is correct.

I don't have a 9i db handy to test on but I tried it on 8i and got the same results as above.

Your 9i example is obviously wrong since the record 4 has gone MIA.

I think you need to go to Metalink at this point - looks like an actual oracle bug.
Suspect you'll need to upgrade your 10g instance and there's probably nothing you can do about the 9i unless there's an existing patch for this problem.
Re: Same query returns differenent result sets in oracle 9i and 10g [message #426766 is a reply to message #426763] Mon, 19 October 2009 08:30 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Run on 9.2.0.8.0 on Linux:
SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle9i Release 9.2.0.8.0 - Production
PL/SQL Release 9.2.0.8.0 - Production
CORE    9.2.0.8.0       Production
TNS for Linux: Version 9.2.0.8.0 - Production
NLSRTL Version 9.2.0.8.0 - Production

SQL> SELECT A.VAL1,B.VAL1 FROM
  2  T_1 A,T_2 B
  3  WHERE A.VAL1=B.VAL1;

      VAL1       VAL1
---------- ----------
         1          1
         2          2

SQL> 
SQL> SELECT A.VAL1,B.VAL1 FROM
  2  T_1 A,T_2 B
  3  WHERE A.VAL1=B.VAL1(+);

      VAL1       VAL1
---------- ----------
         1          1
         2          2
         3
         4
         5

SQL> 
SQL> SELECT A.VAL1,B.VAL1 FROM
  2  T_1 A,T_2 B
  3  WHERE A.VAL1=B.VAL1(+)
  4  AND
  5  NOT EXISTS (SELECT 'X' FROM t_2 C WHERE c.val1=b.val1);

      VAL1       VAL1
---------- ----------
         3
         4
         5


Exactly what you'd expect.

What
Re: Same query returns differenent result sets in oracle 9i and 10g [message #426769 is a reply to message #426758] Mon, 19 October 2009 09:06 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Hi Cookie,

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for 32-bit Windows: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

SQL> SELECT A.VAL1,B.VAL1 FROM
  2  T_1 A,T_2 B
  3  WHERE A.VAL1=B.VAL1(+)
  4  AND
  5  NOT EXISTS (SELECT 'X' FROM t_2 C WHERE c.val1=b.val1)
  6  ;

      VAL1       VAL1
---------- ----------
         3
         4
         5


I also did same test on 10g at home i am getting same result as you explained. Is it possible only one particular database behave differently?

I saw JRowbottom's reply actually i am getting same result on 9i i thinki record 4 missing is cuased by me while copying.

actually we are tryig to migrate our prod db(9.2.0.8.0) to 10g while testing our application on 10g this issue happened and i was investigatin on that.
This is what i feel when i did many test on the actual query, when matching is done in subquery the correlating main query column(here b.val1) is returning null then this issue is happening. if i use nvl(b.val1,0) with any value it is giving me same result as 9i.



Re: Same query returns differenent result sets in oracle 9i and 10g [message #426770 is a reply to message #426758] Mon, 19 October 2009 09:16 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
I assume it's a bug in 10.2.0.1.0.
I'd be having a look on metalink, or just go straight to a higher version - I believe 10.2.0.1.0 had quite a few bugs in it.
Re: Same query returns differenent result sets in oracle 9i and 10g [message #426772 is a reply to message #426758] Mon, 19 October 2009 09:46 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Thanks for your support anyway, i will check that tomorrow and update here.
Re: Same query returns differenent result sets in oracle 9i and 10g [message #426777 is a reply to message #426758] Mon, 19 October 2009 10:19 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Are you sure you need the outer-join?
Re: Same query returns differenent result sets in oracle 9i and 10g [message #427113 is a reply to message #426758] Wed, 21 October 2009 03:32 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
Yeah, you are right this query can be re-written without outer join i think.
Now here i have another question. If i have two tables and i want to compare, for instance a and b,I want to match for a particular column of these tables to fetch records that are not matching, what will be best method for this.
Re: Same query returns differenent result sets in oracle 9i and 10g [message #427117 is a reply to message #426758] Wed, 21 October 2009 03:57 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Asumming you don't just want a != join you're going to have explain that a bit more. Sample tables and expected output would help.
Re: Same query returns differenent result sets in oracle 9i and 10g [message #427121 is a reply to message #426758] Wed, 21 October 2009 04:34 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
select a.val1
from
t_1 a
where
not exists (select 1 from t_2 b where b.val1=a.val1)
ORIONI@DB10GEE $  select a.val1
  2  from 
  3  t_1 a
  4    where
  5   not exists (select 1 from t_2 b where b.val1=a.val1);

     VAL1
---------
        5
        4
        3

i used the same script i posted here.Hope it would be helpful
Re: Same query returns differenent result sets in oracle 9i and 10g [message #427132 is a reply to message #426758] Wed, 21 October 2009 05:10 Go to previous messageGo to next message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
Does that not do what you want?
Re: Same query returns differenent result sets in oracle 9i and 10g [message #427134 is a reply to message #427132] Wed, 21 October 2009 05:21 Go to previous messageGo to next message
yoonus.it@gmail.com
Messages: 109
Registered: June 2007
Location: kuwait
Senior Member
thats what i want but i just wanted to know any better way is there.
Re: Same query returns differenent result sets in oracle 9i and 10g [message #427140 is a reply to message #426758] Wed, 21 October 2009 05:33 Go to previous message
cookiemonster
Messages: 12320
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you're just selecting the one column you can use minus - but I suspect in your real query that's not the case.
Otherwise you can try not in, so long as neither column can be null - doubt you'll see much difference in performance.
Why don't you try the not exists and see if it works for you.
Previous Topic: ORA-02291: integrity constraint violated - parent key not found
Next Topic: Execute Procedure
Goto Forum:
  


Current Time: Sat Sep 24 18:02:56 CDT 2016

Total time taken to generate the page: 0.07820 seconds