Re: Why did Oracle change outer joins in ORACLE7?

In article <k520Lc1w165w_at_vicuna.ocunix.on.ca> frampton_at_vicuna.ocunix.on.ca (Steve Frampton) writes:

> Some of my RPT's don't work anymore under ORACLE7, if they happen to use
> outer joins in selection statements that deal with 'IN' (values_set) or
> 'OR' (conditions). This surprised me, and fixing the problem will
> require a less-than-trivial rewrite of the affected reports.
>
> Why would Oracle wish to restrict use of the (+) join operator in this
> fashion? I wanted to get all my employees, in health-benefit policy
> number order...whether they had benefits or not. The way I handled this
> under Oracle V6 was to ask for a benefit(+) in ('DENTAL','VISION',...)
> and then I would get the information I wanted. Now this is impossible.

I don't know when and why we change the outer join but what you want to do is far from impossible in Oracle7. There are two similar methods with performance implications:

1)

where ...
and (benefit is null or benefit in ('DENTAL','VISION',...)

2)

where ...
and not (benefit is not null and benefit not in ('DENTAL','VISION',...))

As I said, the main difference would be the explain plan chosen though logicwise they are equivalent. tkprof should be able to help you pick the best solution.

--
 Alvin W. Law .............................................. Oracle Corporation
 Project Leader, Cost Management System ........... 300 Oracle Parkway, Floor 6
 Manufacturing Applications .......................... Redwood Shores, CA 94065
 Email: alaw_at_oracle.com ........... Voice: 1.415.506.3390 . Fax: 1.415.506.7299

ORA-03113: end-of-file on communication channel