Re: Can somebody explain this strange sql statement

From: Charles Hooper <hooperc2001_at_gmail.com>
Date: Mon, 18 Jul 2011 15:42:24 -0700 (PDT)
Message-ID: <a40f3a99-c28e-492a-98ac-57dacfea9840_at_v37g2000yqm.googlegroups.com>

On Jul 18, 5:31 pm, "Tony Johansson" <johansson.anders..._at_telia.com> wrote:
> "Charles Hooper" <hooperc2..._at_gmail.com> skrev i meddelandetnews:8cc8133c-b171-444c-b0f3-
> I think that at this point the best course of direction is to throw
> away the SQL statement and start from scratch if either of the
> following is true for the SQL statement:
> * Performs poorly
> * Does not produce the expected results

> Hello
> This sql statement has given the correct result earlier but according to the
> customer will the result from this sql statement
> give wrong result. They say that nothing has been changed the .NET code is
> the same and the sql statement is the same
> but they use a new Oracle version. When a match is supposed to occur for the
> sql select statement we doesn't get any match ?
>
> So do you think it might be possible that two different Oracle versions
> could give different result for the same sql  select statement ?
>
> //Tony
> //Tony
>
> Now to my question do you think that two different Oracle versions could
> give two different results

Simply changing the Oracle release version *should not* have an impact. That said, there are release version specific bugs for ANSI style joins - the query might have worked by coincidence in the past, and when an ANSI bug was fixed, the join order changed. See the following two articles, which list several of the ANSI specific bugs found in Metalink (MOS) and the release versions affected: http://hoopercharles.wordpress.com/2010/12/26/feeling-ansi-about-oracle-join-syntax/ http://hoopercharles.wordpress.com/2010/12/30/ansi-full-outer-join-ready-or-not/

The best bet at this point is probably to try to completely rewrite the SQL statement once it is understood what the SQL statement is supposed to accomplish. I suggest avoiding the ANSI sytle joins if possible and also eliminating the scalar subquery.

Another thought is that some form of new bug in the PL/SQL functions has surfaced. For example, maybe one of those PL/SQL functions referenced in the SQL statement was performing a GROUP BY operation and relying on an implicit ORDER BY, and that implicit ORDER BY no longer happens in the new Oracle Database release version.

Charles Hooper
Co-author of "Expert Oracle Practices: Oracle Database Administration from the Oak Table"
http://hoopercharles.wordpress.com/
IT Manager/Oracle DBA
K&M Machine-Fabricating, Inc. Received on Mon Jul 18 2011 - 17:42:24 CDT