Link to home
Start Free TrialLog in
Avatar of Roberto Madro R.
Roberto Madro R.Flag for United States of America

asked on

Oracle SQL + select from multiple tables while doing (Not Exists) onto another.

I got this sql query that works fine, I select from 2 tables, but I need to (in the Where clause) do a NOT EXISTS from another table yet, and so far it's not going well, your thoughts.

Thx
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

Without the query all we can say is:  That should be possible.

You will also need to define "not going well".  Does it not return any data, not return the correct data or does it catch the keyboard on fire?
Avatar of Roberto Madro R.

ASKER

In this section of the query / script;

*************
select xyz, abc, etc........

from
information2 df,
infomration3 dl
where not exists
(Select * from information3 cl Where df.recordid = cl.recordid and
df.recordid is not null And
df.recordid = dl.recordid (+) And
df.OrdDate <= trunc(to_char(sysdate,'YYYYMMDD')));
spool off

******** I'm getting error ORA-01705: an outer join cannot be specified on a correlation column  *********
Perhaps you might cease using antiquated syntax for joins?

The following is a bit of a guess, but the error message is quite accurate, you can't define the outer join (joining df and dl) inside the NOT EXISTS subquery.

Current ANSI standard syntax:
SELECT
    xyz
  , abc
  , etc........
FROM information2 df
LEFT JOIN infomration3 dl ON df.recordid = dl.recordid
WHERE df.OrdDate <= trunc(sysdate)
AND df.recordid IS NOT NULL
AND NOT EXISTS (
    SELECT
        *
    FROM information3 cl
    WHERE df.recordid = cl.recordid
    )

Open in new window

& now I will now attempt the same in the ancient art:
SELECT
    xyz
  , abc
  , etc........
FROM information2 df, infomration3 dl
WHERE df.recordid = dl.recordid (+)
AND df.OrdDate <= trunc(sysdate)
AND df.recordid IS NOT NULL
AND NOT EXISTS (
    SELECT
        *
    FROM information3 cl
    WHERE df.recordid = cl.recordid
    )

Open in new window


NB: I do not believe you need to use trunc(to_char(sysdate,'YYYYMMDD'))
by itself trunc(sysdate) sets the time to 00:00:00
I know that the optimizer has gotten much better, but in the subquery for the NOT EXISTS, I would change

SELECT *

to

SELECT NULL

Internally, the optimizer may do that now, but it didn't used to and there is a performance penalty for that.  The SELECT * has to do additional I/O to get the entire row, while the SELECT NULL can not only be satisfied using only the index (assuming there is one) but only a single bit needs to be projected.
I believe it is many years since using "select *" when associated with |not| exists has been a performance issue.  
Wasn't that back in Ora 8x days?
I know for sure that it would do it up through at least 9 and probably into 10.  As I said, the optimizer may rewrite your query to avoid that now.  But, the optimizer could choose a different path and not recognize that it does not have to project the whole row.  The optimizer is not infallible, don't trust it to do your job for you.
Ask Tom will disagree... (with the SELECT * alleged performance issue. Tom Kyte has been debunking it for years...and he's bee retired for what, 5 years now?!)

As for "antiquated coding"... Oracle developers used Oracle shorthand for ages. That does not make it antiquated. It makes it efficient.
Writing a "(+)" instead of LEFT OUTER JOIN XXX ON blah blah  = blah... It's obvious to Oracle developers and does not require the elaborate syntax of ANSI.

Coding in ANSI will make your SQL code more "portable", however, if you are a commercial software supplier, portable code should not be your priority...it is a gold-plated waste of your talents. Maintainable code? Good, go for it. Portable? Waste of your time and your client's money.
Older incarnations of the optimizer did access the entire row if you had a SELECT * in the NOT EXISTS.  You could see it in the plan.  It was quite obvious.  As I said, the optimizer has surely changed, but why chance the optimizer changing your query in some way (it does look at thousands of permutations by default) that it would believe it has to project the entire row?  The optimizer isn't perfect, never has been, never will be.  Give it all the help you can.
Sure, if you're running 7.3, and possibly 8i. Don't code to specs which no longer exist. Help the optimizer by writing and testing your code and by understanding explain plan, tk_prof, baselines, adaptive plans,  Give [the optimizer] help where it requires it...not "all the help you can".

To me, that's like perfectionism. I wouldn't hire a perfectionist as they waste too much money on frivolity.
ASKER CERTIFIED SOLUTION
Avatar of Roberto Madro R.
Roberto Madro R.
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial