Bad Plan for LEFT OUTER JOIN having conditions on more than one table
Since the introduction of ANSI-Syntax it is possible to have conditions on more than one table when making an outer join.
Before you would have gotten ORA-1417. But it turns out that there are some issues with execution-plans if you use this.
I created the following simple scenario:
CREATE TABLE base (id number PRIMARY KEY, num number);
insert into base values (1,5);
insert into base values (2,5);
CREATE TABLE deps (id number primary key, base_id number REFERENCES base (id));
create index idx_deps_1 on deps(base_id);
insert into deps values (1,1);
insert into deps values (2,2);
insert into deps values (3,2);
CREATE TABLE oj(id number primary key, deps_id number REFERENCES deps (id), num number);