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
Oracle Database

Avatar of undefined
Last Comment
Roberto Madro R.

8/22/2022 - Mon
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?
ASKER
Roberto Madro R.

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  *********
PortletPaul

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
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
johnsone

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.
PortletPaul

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?
johnsone

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
jtrifts

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.
johnsone

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.
jtrifts

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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
ASKER CERTIFIED SOLUTION
Roberto Madro R.

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question