Re: mix ANSI and Oracle JOINs?

From: Kerry Osborne <kerry.osborne_at_enkitec.com>
Date: Thu, 23 Dec 2010 12:09:41 -0600
Message-Id: <F63EC043-9D4C-4F2B-B406-BCDC4AE47606_at_enkitec.com>

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_at_gmail.com> 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

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Dec 23 2010 - 12:09:41 CST