Re: Optimization of Oracle SQL Statements.

[Albert Howard <starbukk@concentric.net>]

Statement one is the best form.  The reversal of the = clauses is not only
confusing to read, it can cause confusing results in a more complex
statement.  Unfortunately, the style in Statement 2 is a common approach
with PeopleSoft, so lots of people tend to follow that.

-----Original Message-----
From: Heather Simpkins <heather.simpkins@HEROFFICE.COM.AU>
To: Multiple recipients of list SQR-USERS <SQR-USERS@list.iex.net>
Date: Friday, February 12, 1999 2:41 PM
Subject: Optimization of Oracle SQL Statements.


>Hi All,
>
>I can never remember this one - and I know it is dependent on the Database
>you are coding for.  The site I am working at is Oracle - and they have
>staff that write subquery statements in the form:-
>
>select ... from table1 a
>where a.effdt = (select max(effdt) from table1 b
>                where a.key = b.key
>                and a.effdt <= sysdate)
>
>which is WRONG!!!! - it should be either
>
>STATEMENT 1
>select ... from table1 a
>where a.effdt = (select max(effdt) from table1 b
>                where b.key = a.key
>                and b.effdt <= sysdate)
>
>OR
>STATEMENT 2
>select ... from table1 a
>where a.effdt = (select max(effdt) from table1 b
>                where a.key = b.key
>                and sysdate >= b.effdt)
>
>Which is the better for Performance.  I know Statement 1 is the more
logical
>approach - and we are considering making this the standard - to try and
>reduce these problems (lots of trainee SQL on site - not much experience).
>Is there a major performance risk with this standard?
>
>Yes - I do know that Oracle is bottom up so it should be
>
>select ... from table1 a
>where a.effdt = (select max(effdt) from table1 b
>                where b.effdt <= sysdate
>                and b.key = a.key)
>
>but let's tackle one thing at a time....
>
>Thanks in advance.
>
>Heather.
>
>
>
>
>