Oracle View Predicate Pushing and Analytic Functions

Nov 21, 2011   #oracle  #sql 

With predicate pushing the Oracle optimizer ensures that predicates are evaluated as soon as possible when one or more views are stacked upon each other. This often leads to far better performance compared to evaluating the predicates only after querying the view.

There’s one gotcha when using Oracle Analytic Functions and expecting the optimizer to push predicates down the views: It only works when all the predicates that should be pushed down are included in the PARTITION BY clause of the analytic function.

Consider the following example:

CREATE OR REPLACE VIEW TOP_VIEW
AS
SELECT
V.*,
MAX(V.SOME_COL) OVER (PARTITION BY COL0) AS MAXIMUM
FROM BOTTOM_VIEW V;

Depending on the complexity of BOTTOM_VIEW, the following SELECT could lead to dramatic performance issues because the predicates will not be pushed down the view.

SELECT 
T.*
FROM TOPVIEW T
WHERE T.COL1 = 1
AND T.COL2 = 2
AND T.COL3 = 'FOOBAR';

The reason for not pushing the predicates is that they are not included in the partition of the analytic function (MAX(V.SOME_COL) OVER (PARTITION BY COL0) AS MAXIMUM).

There are cases where including your predicates in the partition of the analytic function won’t change the functional output of the query.

So whenever functionally possible, you should define the view like this:

CREATE OR REPLACE VIEW TOP_VIEW
AS
SELECT
V.*,
MAX(V.SOME_COL) OVER (PARTITION BY COL0, COL1, COL2, COL3) AS MAXIMUM
FROM BOTTOM_VIEW V;

This ensures that all three predicates get pushed into the view which leads to far better performance in most cases.