sql - ORACLE left outer join issue (with empty table?) -

sql - ORACLE left outer join issue (with empty table?) -

i'm wondering why query:

select l.objectid left_code, r.object_code right_code oggetto_organizzativo l left outer bring together anag_oggetto_organizzativo r on l.objectid = r.objectid r.objectid null;

returns rows, while other query

select l.objectid left_code oggetto_organizzativo l left outer bring together anag_oggetto_organizzativo r on l.objectid = r.objectid r.objectid null;

doesn't homecoming nothing. problem insert in right table rows nowadays in left table not in right table; @ moment right table empty.

it seems if not referring right field in select clause, oracle not manage outer join.

thanks in advance. paolo

[update] first query

select l.codice_oggetto_sap oggetto_organizzativo l left outer bring together anag_oggetto_organizzativo_sap r on l.codice_oggetto_sap = r.codice_oggetto_sap r.codice_oggetto_sap null

execution plan

second query

select l.codice_oggetto_sap, r.codice_oggetto_sap oggetto_organizzativo l left outer bring together anag_oggetto_organizzativo_sap r on l.codice_oggetto_sap = r.codice_oggetto_sap r.codice_oggetto_sap null

execution plan

the first execution plan statement (null not null) seems me curious...

[second update] @toddlermenot,

but why works???:

create table left_tbl ( "objectid" varchar2(20 byte) ) ; create table right_tbl ( "objectid" varchar2(20 byte) ) ; insert left_tbl (objectid) values ('aaa') left_tbl (objectid) values ('bbb') left_tbl (objectid) values ('ccc') select * dual; select l.objectid left_tbl l left outer bring together right_tbl r on l.objectid = r.objectid r.objectid null;

witt execution plan

plan hash value: 2059691840 -------------------------------------------------------------------------------- | id | operation | name | rows | bytes | cost (%cpu)| time | -------------------------------------------------------------------------------- | 0 | select statement | | 3 | 72 | 5 (0)| 00:00:01 | |* 1 | hash bring together anti | | 3 | 72 | 5 (0)| 00:00:01 | | 2 | table access full| left_tbl | 3 | 36 | 3 (0)| 00:00:01 | | 3 | table access full| right_tbl | 1 | 12 | 2 (0)| 00:00:01 | -------------------------------------------------------------------------------- predicate info (identified operation id): --------------------------------------------------- 1 - access("l"."objectid"="r"."objectid") note ----- - dynamic sampling used statement (level=2)

could , index problem?

you have posted explain plan might differ actual execution plan. difference explain plan optimizer (cbo) thinks follow during actual execution of query might deviate real execution plan.

with said, if explain plans believed, seems optimizer bug hypothesized in comment. reason in first query, cbo taking incorrect shortcut , ignores left outer bring together (the right table not accessed @ all) , uses unusual null not null predicate final filter going false meaning final result set going empty.

to resolve this, might want raise ticket oracle back upwards , patch database bug fixed.

edit: if statistics on both tables out of date, gather stats on both tables , seek rerunning query. cbo might take right plan after stats collected. statistics beingness out of date still not excuse cbo take wrong execution plan. might want after raise ticket oracle support, wrong behavior still reproducible when work on it.

sql oracle