I have a pretty complicated query containing several columns generted
via olap windowing expressions. This query without any 'filtering'
where clauses would run in about 3-4 minutes. However the moment I add
a simple filtering clause like column_a = 20, the query would finish
in less than a second (due to different access plans)
i.e

select <lotsofcolumns>
from <lotsofjoins>
where <severaljoinconditions>
will finish in 4 mins.

select <lotsofcolumns>
from <lotsofjoins>
where <severaljoinconditions>
and column_a = 20 -- < Extra filter
will finish in < 1 sec.

My problem is I cannot put the extra filter in the query as it has to
sit behind a view which would add the required filters as needed

ie. create view view1 as (select <lotsofcolumns>
from <lotsofjoins>
where <severaljoinconditions>
);

select *
from view1
where column_a = 25
still takes 4 mins.

Is there a way to incorporate the additional clauses everytime the
view is run, i.e. somehow always re-compile the view at runtime taking
into account the additional clauses.

This is on DB2 9.1 FP2 on AIX

Thanks in advance for your help!

"Purple-D" <passcore@gmail.com> wrote in message
news:c34d47c3-5a8a-4b30-b636-
> My problem is I cannot put the extra filter in the query as it has to
> sit behind a view which would add the required filters as needed

Why not? Just put the where clause on the SQL that accesses the view.

On Mar 27, 8:42 pm, "Mark A" <nob...@nowhere.com> wrote:
> "Purple-D" <passc...@gmail.com> wrote in message
>
> news:c34d47c3-5a8a-4b30-b636-
>
> > My problem is I cannot put the extra filter in the query as it has to
> > sit behind a view which would add the required filters as needed
>
> Why not? Just put the where clause on the SQL that accesses the view.

It seems to run the 'view' first and then applies the where clause on
the result set and so the access plan is not as optimal as it would be
if the where clause were directly applied to the sql under the view.