This discussion is archived
4 Replies Latest reply: Jan 2, 2013 7:58 AM by Frank Kulash RSS

Same query in 9i & 11g returning different counts

957134 Newbie
Currently Being Moderated
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' )*/ *
FROM TABLE_NAME
WHERE
ORDER BY COL1 DESC , COL2 ASC,COL3 ASC;

Do we have any documentation/material/link which explains this behavior? Any documentation on data type difference between 9i & 11g? Similar issue i observed in connect by prior.I had to select level in order to get the same records but in 9i without selecting level also we were getting desired result.

Thanks
  • 1. Re: Same query in 9i & 11g returning different counts
    BluShadow Guru Moderator
    Currently Being Moderated
    We don't have enough information or your data to know exactly what issue you're referring to.

    9i had bugs... that have been fixed since in 10g and 11g.

    You'd probably be better fixing your query to work with the data, or fix your data, rather than trying to change the query to work as it did on 9i.
  • 2. Re: Same query in 9i & 11g returning different counts
    957134 Newbie
    Currently Being Moderated
    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.
  • 3. Re: Same query in 9i & 11g returning different counts
    BluShadow Guru Moderator
    Currently Being Moderated
    MSINHA wrote:
    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.
    Prove it to us. Show us an example of some data and some example of a query that works differently in 9i and 11g.

    People cannot help you if they don't know what you're referring to.
  • 4. Re: Same query in 9i & 11g returning different counts
    Frank Kulash Guru
    Currently Being Moderated
    Hi,
    MSINHA wrote:
    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' )*/ *
    FROM TABLE_NAME
    WHERE
    ORDER BY COL1 DESC , COL2 ASC,COL3 ASC;
    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.

    Do you have row-level security in either database?

    As Blushadow said, post a complete test script that people can use to re-create the problem and test their ideas. Include CREATE TABLE and INSERT statements for some sample data, and the complete query that you're using. If you are using dbms_rls row-level security, include the function and the code to enable it.
    There's no version 9h or 9j, so it's kind of silly to say you're using 9i. The same goes for 10g. Why not give the actual version numbers, such as 9.2.0.6.0 and 10.2.0.3.0?
    See the forum FAQ {message:id=9360002}

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points