complex view w

Hello,

Please advise regarding Oracle optimizer behavior below:

In complex view with LEFT joins, if we select column which belongs to first table only, CBO still chooses the plan to scan all the tables.

As per my understanding, if only one column of view is used, then no other tables should be joined.

No indexes in this example, no FK's, just tables.

I understand that the proper answer is "not to use view in this case", but there is some application logic, when only view is available.

See example below:

create table A (id number, a varchar2(5));insert into A select rownum, 'A' from dual connect by rownum<100000;create table B(id number, b varchar2(5));insert into B select rownum, 'B' from dual connect by rownum<100000;create table C (id number, c  varchar2(5));insert into C select rownum, 'C' from dual connect by rownum<100000;commit;exec dbms_stats.GATHER_TABLE_STATS (OWNNAME=>user, TABNAME=>'A', METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO', CASCADE=>true);exec dbms_stats.GATHER_TABLE_STATS (OWNNAME=>user, TABNAME=>'B', METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO', CASCADE=>true);exec dbms_stats.GATHER_TABLE_STATS (OWNNAME=>user, TABNAME=>'C', METHOD_OPT=>'FOR ALL INDEXED COLUMNS SIZE AUTO', CASCADE=>true);