Re: ANSI syntax slower than old style?

duncanstreet2004_at_yahoo.com wrote:
> Hello,
>
> Are there times when the new, ANSI syntax for joins is slower than the
> old style or can I assume that ANSI joins are just as fast if done
> properly?
>
> ANSI Joins
> SELECT * from DEPT D
> INNER JOIN EMPLOYEES E
> ON (D.DEPT_ID = E.DEPT_ID)
> WHERE D.DEPT_NAME = 'Human Resources'
>
> Old Style Joins
> SELECT * FROM DEPT D, EMPLOYEES E
> WHERE D.DEPT_ID = E.DEPT_ID
> AND D.DEPT_NAME = 'Human Resources'
>
> Thanks.
>
>
> Robin

Hi,

Just want to share an experience i made when upgrading from 10.1. to 10.2:

I have some materialized views which are based on quite complicated sql statements (more than 100 lines of code) using ANSI syntax. They simply didn't "return" in 10.2: the process handling the query simply ran forever. Tried to trace but the trace file contained only information up to the problematic queries.

It appeared that it had somethign to do with using UNIONs combined with ANSI syntax. So i changed the queries to Oracle syntax which actually removed the problem. The resulting queries also seemed to have an improved performance - but I have to admit that I didn't compare the explain plans. I was just happy things were working again.

Should have filed a service request on metalink probably, but after finding a workaround I lost the motivation to do so. It just seems that Oracle syntax is slightly better supported than ANSI syntax.

regards,
stephan Received on Thu Aug 24 2006 - 02:46:22 CDT