Will the Oracle optimizer still plan a join if I'm not reading any columns in the joined table?

Suppose I have the following view:

CREATE VIEW dummy AS
SELECT a.some_column, b.other_column
FROM a LEFT JOIN b USING (key_column);

When I do a SELECT some_column FROM dummy, is there a way to instruct the optimizer to rewrite the query as SELECT some_column FROM a and skip the join? How?

The decisive factor is whether there is a unique constraint/index on b (key_column).

If there is no unique constraint, there may be duplicate values in b.key_column, so the optimizer has to always make a plan that reads from the table (or an index if there is one on that column).

If on the opposite, there is a unique constraint, the query is equivalent to your rewriting (SELECT some_column FROM a; and the question is whether Oracle has implemented this trick in its optimizer's suite of query transformations.

Indeed it has been implemented - in version 10gR2 and further enhanced in 11gR1 - and it's called Table Elimination. More info can be found in the oracle blog post: Why are some of the tables in my query missing from the plan? The article explains that the 11gR1 enhancement included this particular case (Outer Join Table Elimination) of an outer join to a table with a unique constraint and has an example almost identical to yours.

We can also check with a simple comparison. Notice the difference on the execution plan between the 2 cases in the SQLfiddle:

-- Case 1
create table b
( key_column int                      -- no unique constraint
, other_column int
) ;
insert into b values (1, 1) ;
insert into b values (2, 2) ;
insert into b values (3, 3) ;
insert into b values (4, 4) ;
insert into b values (1, 2) ;      -- some duplicate values
insert into b values (1, 3) ;      -- in key_column

-- Case 2
create table bb
( key_column int primary key       -- has a unique constraint
, other_column int
) ;

insert into bb values (1, 1) ;
insert into bb values (2, 2) ;
insert into bb values (3, 3) ;
insert into bb values (4, 4) ;

-- Both cases
create table a
( some_column int primary key,
  key_column int 
) ;

insert into a values (101, 1) ;
insert into a values (202, 2) ;

We create views for both cases:

CREATE VIEW dummy AS
SELECT a.some_column, b.other_column
FROM a LEFT JOIN b USING (key_column);

CREATE VIEW dummy_bb AS
SELECT a.some_column, bb.other_column
FROM a LEFT JOIN bb USING (key_column);

Now the run the queries, using the view and using the tables - identical except for joining to table b vs. bb where the tables differ only that the second has a unique constraint on key_column:

SELECT some_column FROM dummy ;

SELECT some_column FROM (
    SELECT a.some_column, b.other_column
    FROM a LEFT JOIN b USING (key_column)
);

We see that on case 1 does access table b while in case 2 it does not (using the unexposed transformation).

StackExchange Database Administrators Q#101051, answer score: 9