Hi,

Db2 LUW v9.5 fp5.

Following this thread: http://tinyurl.com/36hhfqf

create view testing_predicate_pushdown as

with
disks as (
select MACHINE_ID, is_external, disk_number,
rownumber() over (partition by MACHINE_ID, disk_number)
as dvnum,
disk_size, volume_size
from disk
)
select
MACHINE_ID,
is_external
,count(distinct disk_number) as count
,sum(case when dvnum = 1 then disk_size else 0 end) as
disk_total
,sum(volume_size) as volume_total
from
disks
group by
is_external;

If I query:

# select * from testing_predicate_pushdown where MACHINE_ID = ?

I get several tablescans, bad access plan and bad response time..

Now, let's remove the OLAP function from the query:

create view testing_predicate_pushdown as

with
disks as (
select MACHINE_ID, is_external, disk_number,
disk_size, volume_size
from disk
)
select
MACHINE_ID,
is_external
,count(distinct disk_number) as count
,sum(volume_size) as volume_total
from
disks
group by
is_external;

then query:
# select * from testing_predicate_pushdown where MACHINE_ID = ?

Good acces plan, IXScans, fast response time.

I've had this problem of not being able to use OLAP function inside
simple views more than once. Usually, using OLAP on the view itself
will come up with a better plan ... But that I want to encapsulate
that SQL complexity inside the view.

Anything obvious that I am missing?

Thanks, -M

More info:

According Serge's post in this other similar topic: http://tinyurl.com/2chufqt

It looks like DB2 should pushdown the predicates contained in a
partition by predicate, which is exactly what I am trying to do here.
However, all I get is tablescans.

Any ideas?

On Nov 24, 6:39*am, Michel Esber <smes... (AT) gmail (DOT) com> wrote:
I have run into this, too, but only with Views. If you run the entire
query in just a query (no view), it will push down the predicate
correct, but with the OLAP in a view, it will not. This has been
frustrating in the past.

I've last verified it in v9.1. (I haven't verified it in later
releases since we removed the offending views before the migration).

-Chris

Hi Chris,

Thanks for the info.

Have you opened a PMR for this sub-optimal plan ?

Do you think IBM will threat this if a PMR is opened?

Thanks

No, I did not open a PMR - instead I found a way around it (stopped
using the view). At the time we had other items we really wanted IBM
to look at instead.

I would expect that if a PMR was opened on this, it would be addressed
at aome point.

-Chris