This improvement addresses a problem of transferring
local predicates from a table on one side to the same
table on the other side of a join. By transferring the local
predicates to the other side, the application of local
predicates can filter rows prior to performing the join thereby
leading to performance gains.
The following conditions must be met for this improvement:
1. The join columns to the table must be defined in a unique
index.
2. The join is between a base table and a union view.
3. There are local predicates on the table on one side of the
join.
4. On the other side of the join, the join columns can be mapped
to the same table.
Example:
create table t1(c1 int, c2 int);
create unique index i1 on t1(c1);
create table big1(x1 int, x2 int, x3 int);
create table big2(x1 int, x2 int, x3 int);
with big_vw(x1,x2,x3,c1) as
(select big1.*, t1.c1 from big1, t1 where big1.x1 = t1.c1
union
select big2.*, t1.c1 from big2, t1 where big2.x1 = t1.c1)
select * from big_vw, t1 where big_vw.c1 = t1.c1 and t1.c2 = 1;
With this improvement, the transfer of t1.c2=1 into big_vw will
result
in earlier filtering in the joins between tables big1 and t1 and
between big2 and t1. |
****************************************************************
* USERS AFFECTED: *
* All *
****************************************************************
* PROBLEM DESCRIPTION: *
* DB2 Rewrite will optimize the performance of union all views *
* by performing pushdown of local predicates of a base table *
* which *
* joins the union all view if the join is considered to be non *
* expanding. *
* *
* *
* *
* For the rewrite the following conditions must be true: *
* *
* 1. If we have join predicates from base table that cover all *
* the columns in a unique index of Base Table, and, *
* *
* 2. those columns, via equality classes in various levels, *
* can be mapped to all the branches of the UA that also ranges *
* over the *
* same base table (BT) *
* *
* *
* *
* Result: *
* *
* local predicates on Base Table are pushed down to all the *
* Base Tables under Union All view. *
****************************************************************
* RECOMMENDATION: *
* Upgrade to DB2 v9.5 fix pack 6 *
**************************************************************** |
