Does Oracle Query Optimizer apply top level where clauses to sub queries or views?

Question

Oracle documentation says Oracle query engine executes sub-queries and views first and then executes top-level query. As a natural result of this, Oracle does not allow you to reference field values of top-level query in sub-queries (MSSQL allows this) You need to build a self sufficient sub-query and join the results to the top-level query.

After that comes my question: Does Oracle query optimizer apply top-level query's "where clauses" to sub-queries (where applicable) during execution?

Let's say I have a sub-query or view that returns a million rows when run alone but when joined with the top-level query only 1000 of those rows will be used, due to the join clause or where clause of the top-level query. Does Oracle try to bring all million rows from the sub-query and filter out unnecessary rows during joins or does Oracle query optimizer move join clauses or where clauses from the top-level query to sub-query so bring only a subset of rows?

Please do not give me the obvious answer saying: "Such a query is a poorly written one and I need to rewrite my query". Sometimes there are technical or non-technical limitations at play so I might not be able to do that.

I know that MSSQL query optimizer does this for sub-queries and views but since Oracle says sub-queries will be executed first, I needed to ask. Does Oracle query optimizer do this?

Edit: The following can be used as a sample query. The query might not be logically sound but it does represent a sample for my question. The sub-query returns all sales but the top-level query is using only the ones in this year. Does Oracle calculate sums of all sales or just the ones in this year?

select u.user_fullname, s.date, s.total
from users u
     inner join ( select userID, date, sum(total) as total       
                 from sales
                 group by userID, date
               ) s on s.userID = u.userID
where s.date > '2015-01-01'

The Oracle optimizer can and does do all sorts of things, but your question seems to be more about a perceived limitation in the SQL syntax. Hard to answer without the actual query, however. — Jeffrey Kemp, Oct 15 '15 at 08:10
Your 1st claim is wrong, of course you can do Correlated Subqueries in Oracle. The optimizer will also try to push conditions into Derived Tables/Subqueries or across joins if it's known that the result is still the same. But your example is actually poorly written, as the `WHERE`-condition changes the result of the join from Outer to Inner. Look at the optimizer's plan to see what actually happens. — dnoeth, Oct 15 '15 at 08:56
"*Does Oracle calculate sums of all sales or just the ones in this year?*"Oracle will actually tell you that - just check the execution plan. — , Oct 15 '15 at 20:01

score 1 · Accepted Answer · answered Oct 15 '15 at 18:30

Oracle frequently moves conditions between different levels of a query. This is called predicate pushing. Logical limitations may prevent subqueries and inline views from referencing top-level items, but that limitation does not apply to the transformed queries that Oracle executes.

This feature has been in Oracle for a long time. There are many references to it in the documentation, and even a few hints to help control it. (Although in general you won't need to use the hints.) As far as I know there are no sources that explain exactly when it can work but it should be able to work in your example. There are many cases where predicate pushing is possible, but not enabled, because the optimizer does not think it makes sense.

This simple example shows predicate pushing in action:

--Create a simple table.
drop table test1;
create table test1(a number primary key, b number);
insert into test1 select level, 1 from dual connect by level <= 100000;
commit;
begin
    dbms_stats.gather_table_stats(user, 'TEST1');
end;
/

--Create a very slow function.
create or replace function very_slow(p number) return number authid current_user is
begin
    execute immediate 'begin dbms_lock.sleep(1); end;';
    return 1;
end;
/

--This query only takes 1 second.
--Without predicate pushing it would take hours. 
select *
from
(
    select *
    from test1
    where b = very_slow(b)
)
where a = 1;

Does Oracle Query Optimizer apply top level where clauses to sub queries or views?

1 Answers1