Does Oracle's Optimizer Take into Consideration a Subquery's Underlying Columns?

Question

Say you have a query like so:

with subselect as (
   select foo_id 
     from foo
) 
select bar_id 
  from bar
  join subselect on foo_id = bar_id
 where foo_id = 1000

Imagine you have an index on foo_id. Is Oracle's database smart enough to use the index in the query for the line "where foo_id = 1000"? OR since foo_id is wrapped in a subquery, does Oracle lose the index information related to this column?

Answer 1

Perform a simple test:

create table foo as
select t.object_id as foo_id, t.* from all_objects t;

create table bar as
select t.object_id as bar_id, t.* from all_objects t;

create index foo_id_ix on foo(foo_id);

exec dbms_stats.GATHER_TABLE_STATS(ownname=>user, tabname=>'FOO', method_opt=>'FOR ALL INDEXED COLUMNS' );

explain plan for 
with subselect as (
   select foo_id 
     from foo
) 
select bar_id 
  from bar
  join subselect on foo_id = bar_id
 where foo_id = 1000;

 select * from table( DBMS_XPLAN.DISPLAY );

and a result of last query is:

Plan hash value: 445248211

----------------------------------------------------------------------------------
| Id  | Operation            | Name      | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------
|   0 | SELECT STATEMENT     |           |     1 |    10 |   366   (1)| 00:00:01 |
|   1 |  MERGE JOIN CARTESIAN|           |     1 |    10 |   366   (1)| 00:00:01 |
|*  2 |   TABLE ACCESS FULL  | BAR       |     1 |     5 |   365   (1)| 00:00:01 |
|   3 |   BUFFER SORT        |           |     1 |     5 |     1   (0)| 00:00:01 |
|*  4 |    INDEX RANGE SCAN  | FOO_ID_IX |     1 |     5 |     1   (0)| 00:00:01 |
----------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("BAR_ID"=1000)
   4 - access("FOO_ID"=1000)

In the above example Oracle uses |* 4 | INDEX RANGE SCAN using index: FOO_ID_IX for filter 4 - access("FOO_ID"=1000)

So the answer is:
yes, the Oracle's database is smart enough to use the index in the query for the line "where foo_id = 1000"