Re: Can somebody explain this strange sql statement
From: onedbguru <onedbguru_at_yahoo.com>
Date: Mon, 18 Jul 2011 17:37:02 -0700 (PDT)
Message-ID: <18a3f5f5-ce5e-4ff8-86e6-fe5a46dc8ed3_at_h4g2000vbw.googlegroups.com>
On Jul 18, 6:42 pm, Charles Hooper <hooperc2..._at_gmail.com> wrote:
> 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-orac...http://hoopercharles.wordpress.com/2010/12/30/ansi-full-outer-join-re...
>
> 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.
Date: Mon, 18 Jul 2011 17:37:02 -0700 (PDT)
Message-ID: <18a3f5f5-ce5e-4ff8-86e6-fe5a46dc8ed3_at_h4g2000vbw.googlegroups.com>
On Jul 18, 6:42 pm, Charles Hooper <hooperc2..._at_gmail.com> wrote:
> 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-orac...http://hoopercharles.wordpress.com/2010/12/30/ansi-full-outer-join-re...
>
> 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.
Looks like this guy posted this in quite a few NG and forums... Could be wrong, but to me it looks like he is trying to reverse-engineer something (some web-based app??) without a clue as to what it needs to do... Received on Mon Jul 18 2011 - 19:37:02 CDT