By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,247 Members | 1,057 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,247 IT Pros & Developers. It's quick & easy.

access plan when view has rank()

P: n/a
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

Nov 12 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
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
Nov 12 '05 #2

P: n/a
Thanks, Serge!

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

Andy

Nov 12 '05 #3

P: n/a
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
Nov 12 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.