CODEWITHSUNDEEP
sqloracle-databaseoracle12c
Pete
Pete

Reputation: 51

SQL Query returning different results on same Oracle database on 2 different systems

I have a 2 identical databases running on Oracle 12c. They are sitting on 2 different servers.

I'm executing the following query using SQL Developer on both databases. 

select *
from sla
where objectname = 'INCIDENT' and
      status in 'ACTIVE'  and
      (exists (select 1
               from classancestor
               where ancestor=sla.classstructureid and classancestor.classstructureid= '')  or
       classstructureid is null or
       classstructureid =  ''
      ) ;

I get 260 results on one of the databases and 0 results on the other. This query is generated by an ERP we are using so I cannot modify the SQL itself.

One thing I noticed is that if I remove the parentheses surrounding the exists operator, the behaviour changes.

Can someone help? Is there a precedence when using OR? Is there an Oracle patch that has an impact on how SQL statements are processed?

Thanks

Upvotes: 3

Views: 1740

Answers (1)

Pete
Pete

Reputation: 51

Ok, for now I simply reverted to Oracle 11g optimizer and solved my issue.

ALTER SYSTEM SET optimizer_features_enable='11.2.0.3';

Not my favorite solution, but it's a workaround for now.

Thanks for your help guys

Upvotes: 2

Related Questions