SQL Language (MOSC)

MOSC Banner

Bad Plan for LEFT OUTER JOIN having conditions on more than one table

edited Dec 16, 2012 9:56PM in SQL Language (MOSC) 13 commentsAnswered
Hello,

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);

Howdy, Stranger!

Log In

To view full details, sign in to My Oracle Support Community.

Register

Don't have a My Oracle Support Community account? Click here to get started.

Category Leaderboard

Top contributors this month

New to My Oracle Support Community? Visit our Welcome Center

MOSC Help Center