Re: mix ANSI and Oracle JOINs?
- From: Kerry Osborne <kerry.osborne@xxxxxxxxxxx>
- To: jkstill@xxxxxxxxx
- Date: Thu, 23 Dec 2010 12:09:41 -0600
I believe you wrote a whole chapter on Subquery Factoring in a recently
released Apress book, right? ;)
I have to say that I find ANSI join syntax considerably less clear in its
intent. I also seem to see more errors with it than with the traditional Oracle
join syntax. I'm not sure whether that's due to the syntax itself or the
developers that tend to use it. But I do think the fact that it is less
straight forward contributes to the mistakes. I'm not sure Oracle even directly
supports mixing the syntax by the way (I would not want to combine them, even
if it is documented). I actually saw an example this week. It was a very simple
statement, so the error is easy to spot. But in more complicated examples with
many tables and many join conditions it can be difficult to locate the problem.
Here's what I saw this week (cleaned up to protect the guilty):
select ...
from table_1 a left join table_2 b on a.name = b.name, table_3 c
where ...
Unfortunately, table_3 had about 40 million records. The query ran for a few
hours before it got killed.
Kerry Osborne
Enkitec
blog: kerryosborne.oracle-guy.com
On Dec 23, 2010, at 11:38 AM, Jared Still wrote:
> On Thu, Dec 23, 2010 at 8:08 AM, Kenneth Naim <kennethnaim@xxxxxxxxx> wrote:
> I also find the ansi syntax difficult to read and format when
> inline views are present.
>
>
> this is easily rectified by using query subfactoring (WITH clause) rather
> than
> inline views.
>
> I've never like inline views simply because they make the queries so much
> more difficult to understand.
>
>
> Jared Still
> Certifiable Oracle DBA and Part Time Perl Evangelist
> Oracle Blog: http://jkstill.blogspot.com
> Home Page: http://jaredstill.com
Other related posts:
