access plan when view has rank()

[bughunter]

I discover next problem

I have view definition with rank()

create view vTEST as
select c1, c2, c3, ...
-- problem area start
, rank() over (order by c3) as RNK
-- problem area stop
from table
where [low selectivity predicates: c4=v1 and c5=v2 ]

when I do :
select * from vTEST where C1 = :value;

access plan and query is very poor, because predicate C1=value doesn't
used db2. When I do simple select like view definition predicate
C1=value used and query work fine! Identically select and view has
different access plan... Selectivity clause doesn't work with view.

select * from vTEST where C1 = :value
will also work fine if I remove column definition with rank() function.

Tested on DB2 8.2 fixpack 7 and 8 under win32

Bug or feature?

Andy
bughunter

[Serge Rielau]

bughunter@ru wrote:

I discover next problem

I have view definition with rank()

create view vTEST as
select c1, c2, c3, ...
-- problem area start
, rank() over (order by c3) as RNK
-- problem area stop
from table
where [low selectivity predicates: c4=v1 and c5=v2 ]

when I do :
select * from vTEST where C1 = :value;

access plan and query is very poor, because predicate C1=value doesn't
used db2. When I do simple select like view definition predicate
C1=value used and query work fine! Identically select and view has
different access plan... Selectivity clause doesn't work with view.

select * from vTEST where C1 = :value
will also work fine if I remove column definition with rank() function.

Tested on DB2 8.2 fixpack 7 and 8 under win32

Bug or feature?

Andy
bughunter

Feature. You are not comparing apples to apples.
The equivalent select is:
select * from
(select c1, c2, c3, ...
-- problem area start
, rank() over (order by c3) as RNK
-- problem area stop
from table
where [low selectivity predicates: c4=v1 and c5=v2 ]) AS X
WHERE where C1 = :value

Lets' assume c1 is a primary key (for arguments sake), then
RNK would always be 1 if c1 were pushed down.
However RNK could be any number between 1 and #rowsintable without the
pushdown.

If you want to push the predicate down you may want to consider using an
SQL table function:
CREATE FUCNTION vTEST(INT c1_arg) RETURNS TABLE (....)
READS SQL DATA
RETURN SELECT(select c1, c2, c3, ...
-- problem area start
, rank() over (order by c3) as RNK
-- problem area stop
from table
where [low selectivity predicates: c4=v1 and c5=v2 ]) AS X
AND C1 = c1_arg;

SELECT * FROM TABLE(vtest(:hv )) AS V;

That should give you a good plan.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[bughunter]

Thanks, Serge!

What about another constructions where select and select from view will
have different access plan?

Andy

[Serge Rielau]

bughunter@ru wrote:

Thanks, Serge!

What about another constructions where select and select from view will
have different access plan?

Andy

The common case is that there is a semantic reason whenever the
injection of a view has a detrimental effect.
There are exceotions, but ther are far and few between ( and I don't
know them off hand). Personally I treat them as optimization bugs.

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab