select rownumber() over(), ... affecting plan very badly.

[Paul Reddin]

Hi,

using ROWNUMBER() is affecting the plan of a view very badly...

is there a way of writing the following view to ensure rownumber()
is done as the last thing done?
i.e after the calling predicates have been applied to a_cte

e.g
I have rewritten the view as follows using a CTE, trying to
'influence' the rownumber()to be run last, but this doesn't help?

create view
as
with a_cte(.......)
select rownumber() over(), a_cte.* from a_cte;
-- This runs in 40seconds when called with predicates on a_cte

create view
as
with a_cte(.......)
select a_cte.* from a_cte;
-- This runs in 0.03seconds when called with predicates on a_cte

Thanks.

Paul.

[Paul Vernon]

"Paul Reddin" <pa**@abacus.co.uk> wrote in message
news:1f**************************@posting.google.c om...

Hi,

using ROWNUMBER() is affecting the plan of a view very badly...

is there a way of writing the following view to ensure rownumber()
is done as the last thing done?
i.e after the calling predicates have been applied to a_cte
Views don't work that way. I.e.

SELECT rownumber() over() rn, t.* FROM t WHERE col1 = 'A'

is a different query than

CREATE VIEW view AS (SELECT rownumber() over() rn, t.* FROM t );
SELECT * FROM view WHERE col1 = 'A';

as that is equivalent to this

SELECT * FROM (
SELECT rownumber() over() rn, t.* FROM t
) WHERE col1 = 'A'

Now, an inner query can reference an outer value, such as

WITH p(param1) AS (VALUES('A'))
SELECT * FROM (
SELECT rownumber() over() rn, t.* FROM t, p
WHERE col1 = param1
) v, p
WHERE col1 = param1
;

But it's no good creating a view such as

CREATE VIEW v AS (
SELECT rownumber() over() rn, t.* FROM t, p
WHERE col1 = param1
)

because p is unresolved and vies cannot have unresolved parameters (in fact,
views cannot have parameters full stop)

So your only solution, short of hoping for parameterised views (whatever
that would mean) in some future SQL standard, is a table function...

create table t (id int primary key , col1 char(1));
insert into t values(1,'A'),(2,'B'),(3,'A'),(4,'C'),(5,'B');

CREATE FUNCTION T.RN (x varchar(1))
RETURNS TABLE (rn int, id int, col1 char(1))
LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC
RETURN
SELECT rownumber() over() rn, t.* FROM t WHERE col1 = x
;
select * from table(T.RN(CHAR('A'))) as rn
;

Regards
Paul Vernon
Business Intelligence, IBM Global Services

e.g
I have rewritten the view as follows using a CTE, trying to
'influence' the rownumber()to be run last, but this doesn't help?

create view
as
with a_cte(.......)
select rownumber() over(), a_cte.* from a_cte;
-- This runs in 40seconds when called with predicates on a_cte

create view
as
with a_cte(.......)
select a_cte.* from a_cte;
-- This runs in 0.03seconds when called with predicates on a_cte

Thanks.

Paul.

[Serge Rielau]

Paul,

How do use the view?
If you simply do a select * from v
there shoucln't be a problem.
But as soon as you add a predicate;
select * from v WHERE c1 = 5
This predicate which would ordinarily find it's way down to the table to
be used in a scan or index fetch cannot be pushed past teh rownumber()
function becuase you would get different results.

Now on another note, if you did NOT select the result of the rownumber()
DB2 should drop it out of the plan. If that fails to happen (the
optimized SQL in db2exfmt would show) I would see that as a problem to
look into.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[PM \(pm3iinc-nospam\) CGO]

As far as i can remember, cte's are evaluated in early stage to be able to
share/reuse them later.
Where did you put the rownumber() in your 2nd example?
Are you doing some kind of recursion?

PM

"Paul Reddin" <pa**@abacus.co.uk> a écrit dans le message de
news:1f**************************@posting.google.c om...

Hi,

using ROWNUMBER() is affecting the plan of a view very badly...

is there a way of writing the following view to ensure rownumber()
is done as the last thing done?
i.e after the calling predicates have been applied to a_cte

e.g
I have rewritten the view as follows using a CTE, trying to
'influence' the rownumber()to be run last, but this doesn't help?

create view
as
with a_cte(.......)
select rownumber() over(), a_cte.* from a_cte;
-- This runs in 40seconds when called with predicates on a_cte

create view
as
with a_cte(.......)
select a_cte.* from a_cte;
-- This runs in 0.03seconds when called with predicates on a_cte

Thanks.

Paul.

[Paul Reddin]

Paul,

Thanks for the explanation, I don't follow it 100%, but it looks
like a familiar problem we have running across continuously with
views, that is, the predicate push down isn't always as hoped for!

As background, we are using views extensively to encapsulate Objects,
which works fine 98% of the time, but sometimes our queries/views are
painful/time consuming to tune/write correctly!

Many Thanks.

PaulR.

[Paul Reddin]

Serge Rielau <sr*****@ca.eye-be-em.com> wrote in message news:<ca**********@hanover.torolab.ibm.com>...

This predicate which would ordinarily find it's way down to the table to
be used in a scan or index fetch cannot be pushed past teh rownumber()
function becuase you would get different results.
OK, this makes sense now.
Now on another note, if you did NOT select the result of the rownumber()
DB2 should drop it out of the plan. If that fails to happen (the
optimized SQL in db2exfmt would show) I would see that as a problem to
look into.

I have tested this, and the optimiser is fine when I don't actually
select the rownumber, not much use having it in the view then though :-)

Many thanks.

Paul.