I think I'd need some evidence for that, because here's an example showing that the optimizer is fine with that kind of scenario
SQL> drop tablespace demo including contents and datafiles;
Tablespace dropped.
SQL> drop table t1 cascade constraints purge;
Table dropped.
SQL> drop table t2 cascade constraints purge;
Table dropped.
SQL> drop table t3 cascade constraints purge;
Table dropped.
SQL> drop table t4 cascade constraints purge;
Table dropped.
SQL>
SQL> create table t1 as select * from dba_objects;
Table created.
SQL> create table t2 as select * from dba_objects;
Table created.
SQL> create table t3 as select * from dba_objects;
Table created.
SQL> create table t4 tablespace large_ts nologging as select * from dba_objects;
Table created.
SQL>
SQL> insert /*+ APPEND */ into t4 select * from t4;
104185 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
208370 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
416740 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
833480 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
1666960 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
3333920 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
6667840 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
13335680 rows created.
SQL> commit;
Commit complete.
SQL> insert /*+ APPEND */ into t4 select * from t4;
26671360 rows created.
SQL> commit;
Commit complete.
SQL> create index t4ix on t4 ( object_name ) tablespace large_ts nologging;
Index created.
SQL> exec dbms_stats.gather_table_stats('','T4',cascade=>false)
PL/SQL procedure successfully completed.
SQL> set autotrace traceonly explain
SQL> select * from v
2 where tname = 'T2';
Execution Plan
----------------------------------------------------------
Plan hash value: 289899379
-----------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 104K| 36M| 312 (1)| 00:00:01 |
| 1 | VIEW | V | 104K| 36M| 312 (1)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
| 4 | TABLE ACCESS FULL| T1 | 104K| 11M| 312 (1)| 00:00:01 |
| 5 | TABLE ACCESS FULL | T2 | 104K| 11M| 312 (1)| 00:00:01 |
|* 6 | FILTER | | | | | |
| 7 | TABLE ACCESS FULL| T3 | 104K| 11M| 312 (1)| 00:00:01 |
|* 8 | FILTER | | | | | |
| 9 | TABLE ACCESS FULL| T4 | 53M| 5799M| 155K (1)| 00:00:07 |
-----------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
6 - filter(NULL IS NOT NULL)
8 - filter(NULL IS NOT NULL)
SQL>
SQL>
SQL> set autotrace traceonly explain
SQL> select * from v
2 where tname = 'T4'
3 and object_name = 'T4';
Execution Plan
----------------------------------------------------------
Plan hash value: 2442212741
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 904 | 328K| 645 (0)| 00:00:01 |
| 1 | VIEW | V | 904 | 328K| 645 (0)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | FILTER | | | | | |
|* 4 | TABLE ACCESS FULL | T1 | 2 | 228 | 311 (1)| 00:00:01 |
|* 5 | FILTER | | | | | |
|* 6 | TABLE ACCESS FULL | T2 | 2 | 228 | 311 (1)| 00:00:01 |
|* 7 | FILTER | | | | | |
|* 8 | TABLE ACCESS FULL | T3 | 2 | 228 | 311 (1)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID BATCHED| T4 | 901 | 100K| 645 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | T4IX | 901 | | 8 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter(NULL IS NOT NULL)
4 - filter("T1"."OBJECT_NAME"='T4')
5 - filter(NULL IS NOT NULL)
6 - filter("T2"."OBJECT_NAME"='T4')
7 - filter(NULL IS NOT NULL)
8 - filter("T3"."OBJECT_NAME"='T4')
10 - access("T4"."OBJECT_NAME"='T4')