MSINHA wrote:Prove it to us. Show us an example of some data and some example of a query that works differently in 9i and 11g.
Why to fix data? Same set of data was exported from 9i to 11g during upgrade.Its only the query whose results are different. It means 11g treats some of the datatype differently as i explained for NULL.
MSINHA wrote:If you're getting any results other than an error message, then I don't believe you're running the query above. You need include some condition in the WHERE clause, or leave out the WHERE clause altogether.
Recently our DB was upgraded from 9i to 11g. We observed some weird results like same query returning more records in 11g than in 9i. I later found out that was because of Null values. I used below commands to achieve same results.
1) ALTER session set optimizer_features_enable='9.2.0'; -
2) SELECT /*+ optimizer_features_enable=('9.2.0' )*/ *
ORDER BY COL1 DESC , COL2 ASC,COL3 ASC;