Re: What hint would you try?
From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Fri, 10 Jun 2011 09:47:11 -0500
Message-Id: <6CAA5393-B187-4B2E-80A6-FE03933F7E8D_at_enkitec.com>
That's funny. I'll have to remember that one. I do find ANSI syntax much less clear in anything other than trivial statements and I would think it's harder for the Oracle code to figure out what to do with it as well. (maybe that's part of problem) My experiences have been similar to yours though, seeing numerous situations where ANSI syntax confused Oracle, normal syntax did not. While I'm sure the code is getting better, I don't think there is any real advantage to using it other than the full outer join part. The fact that Oracle often (always?) transforms it to normal oracle join syntax before coming up with a plan (you can see this in a 10053 trace) also makes me think that just writing it that way to begin with eliminates some of the possibility for problems. I should also mention that I've seen several situations where a developers mixed Oracle syntax with ANSI syntax which really makes me nervous.
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
Date: Fri, 10 Jun 2011 09:47:11 -0500
Message-Id: <6CAA5393-B187-4B2E-80A6-FE03933F7E8D_at_enkitec.com>
That's funny. I'll have to remember that one. I do find ANSI syntax much less clear in anything other than trivial statements and I would think it's harder for the Oracle code to figure out what to do with it as well. (maybe that's part of problem) My experiences have been similar to yours though, seeing numerous situations where ANSI syntax confused Oracle, normal syntax did not. While I'm sure the code is getting better, I don't think there is any real advantage to using it other than the full outer join part. The fact that Oracle often (always?) transforms it to normal oracle join syntax before coming up with a plan (you can see this in a 10053 trace) also makes me think that just writing it that way to begin with eliminates some of the possibility for problems. I should also mention that I've seen several situations where a developers mixed Oracle syntax with ANSI syntax which really makes me nervous.
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Jun 10, 2011, at 8:41 AM, Wolfgang Breitling wrote:
> I should have added - neither do I.
>
> When a developer comes to me for help tuning a sql with ANSI join syntax and I can't see the solution within about 5 minutes I tell them to come back with the sql rewritten in Oracle join syntax because neither I nor the optimizer speak ANSI.
>
> On 2011-06-10, at 7:33 AM, Wolfgang Breitling wrote:
>
>> the optimizer as of current doesn't understand it very well
>
> --
> http://www.freelists.org/webpage/oracle-l
>
>
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Jun 10 2011 - 09:47:11 CDT