Suppose I have the following view:

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?

  • 2
    I'm no expert in Oracle's optimizing but I think it does just that (I mean read the other_column values). Can you check the plan (and post it, editing your question)? Buit it cannot skip the join entirely. At least the index on b (key_column) has to be read. If there are duplicate values, the query has to show duplicate results. – ypercubeᵀᴹ May 9 '15 at 13:57
  • 2
    Now if b (column) has a unique constraint, it would not de required and that poses an interesting problem. Haas Oracle implemented this optimization? – ypercubeᵀᴹ May 9 '15 at 14:00

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:

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

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).

  • So Oracle can prove that the outer join will return 0 or 1 rows due to the unique constraint and therefore can remove the join entirely. Neat. PS I don't like those Flash execution plans, any way to get the plain text versions? – Colin 't Hart May 9 '15 at 18:13
  • @Colin'tHart There are only two options in SQL-Fiddle, for Oracle plans: Graphical and Tabular. You can click on the top left to see the tabular output. if you then click on the (SELECT STATEMENT or HASH JOIN or ..) you'll see the plain text for that part, in an extra window on the bottom right. – ypercubeᵀᴹ May 10 '15 at 0:40

When you do a SELECT ... FROM some_view, the first thing Oracle will do is substitute some_view with SELECT statement that defines the view. So in your case,

SELECT some_column FROM dummy

is, both in logical and optimizer terms, the same as

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

Version I have on my personal computer ( express) does not skip the join, although, if there is an index on key_column in b table, it will use the index for the join instead of the table. You can check your exact behaviour by using GATHER_PLAN_STATISTICS hint, see this article for more information.

As far as I know, there is no hint to make the database skip the join. However, from my experience, if you aim for performance, it is best to skip selecting from views and always do selects directly on tables, coding the exact SQL you need for the specific purpose (which in your case means: simply write SELECT some_column FROM a). I understand why views appeal to you, but unfortunately, data model abstractions and optimal SQL are not very good friends.

  • That has been my experience as well (12.1). Pity; we're forced to used views for modularity reasons, and some queries really suffer from it. – Mihai May 9 '15 at 17:18

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.