Predicate Pushdown on Views with OLAP

Michel Esber wrote:
> Hello,
>
> DB2 LUW V9.5 FP3.
>
> Whenever I include rownumber on the outer select of a view, my access
> plan is very slow and has a lot of tablescans.
>
> # select machine_id, sw_id, homologation_status, type from
> VIEW_WITH_OLAP_INSIDE where MACHINE_ID=?
>
> If I remove the OLAP function from the view body, I get excellent
> speeds:
>
> # select machine_id, sw_id, homologation_status, type, rownumber ()
> over (partition by machine_id, sw_id order by type) as rn from
> VIEW_WITHOUT_OLAP where id = '?'
>
> The *only* difference between both statements is where my OLAP
> function is placed. The first one is used inside the view, the second
> one is used in a view without any OLAP.
>
> Also, the first access plan has a much higher cost than the second
> one. Are there any known limitations on using Views and OLAP ?
Michel,

This is all about semantics.
Now, your examples are a bit cobbled together (= '?' ??) so let's look
at a scenario:

CREATE TABLE T(c1);
INSERT INTO T VALUES 10, 20, 30, 40;

SELECT ROW_NUMBER() OVER(ORDER BY c1) AS rn, c1 FROM T WHERE c1 = 30;
=> (1, 30)
The where clause executes before teh select list. All is well.

WITH V AS (SELECT ROW_NUMBER() OVER(ORDER BY c1) AS rn, c1 FROM T)
SELECT rn, c1 FROM V WHERE c1 = 30
=> (3, 30) -- <--- Three!
You get what you ask for here. You asked for an resultset "V" with all
rows ordered. Then of that resultset you asked to get the one with c1 = 30.
If DB2 pushed the predicate down then the answer would change. The
optimizer must never change the result. That's a cardinal rule of RDBMS.

Now if you do:
WITH V AS (SELECT ROW_NUMBER() OVER(ORDER BY c1) AS rn, c1 FROM T)
SELECT c1 FROM V WHERE c1 = 30
=> (30)
In this case the rn column has not been used and DB2 can toss it out.
With the rn column gone the predicate can be pushed down.
(and I hope that is what you see, otherwise there is indeed a problem).

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
 >> Stay informed about: Predicate Pushdown on Views with OLAP 

ChrisC wrote:
> That makes sense when you are talking about the columns in the ORDER
> BY clause of row_number(). However, Michel was talking about the
> columns in the PARTITION BY clause, which I would think would be safe
> to push down:
True enough, yes predicates on GROUP BY/PARTITION BY can/could be pushed
down.
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
 >> Stay informed about: Predicate Pushdown on Views with OLAP