Branch elimination for a query on a UNION ALL View does not work as expected

[Joachim Klassen]

Hi all,

I'm investigating partitioned tables using a UNION ALL VIEW and found
the following (see ddl below):
If I create a check constraint like "check (month(tdate) = 1)" DB2
won't do branch elimination for a query like "SELECT * FROM Q1 WHERE
TDATE = '01.01.2004'". Explains shows that all 3 tables are accessed.
But with a a constraint "check (tdate between '01.01.2004' and
'31.01.2004')" DB2 will do branch elimination - means only tab_01 is
accessed.
I tried this with QUERYOPT 5,7 and 9 - same result.
I saw some similar threads for this theme - but no answer (neither
"works as designed" nor "DB2 can't do this at the moment")

Any comments ?

Details:
db2level: DB21085I Instance "DB2" uses "32" bits and DB2 code release
"SQL08020" with level identifier "03010106".
Informational tokens are "DB2 v8.1.7.445", "s040812", "WR21342", and
FixPak "7".

create table tab_01
(tdate date not null
,col1 integer not null
,col2 char(10) not null
) in userspace1;
create table tab_02 like tab_01 in userspace1;
create table tab_03 like tab_01 in userspace1;

alter table tab_01 add constraint tab_01_chk check (month(tdate) = 1);
alter table tab_02 add constraint tab_02_chk check (month(tdate) = 2);
alter table tab_03 add constraint tab_03_chk check (month(tdate) = 3);
create index tab_01_x1 on tab_01 (tdate);
create index tab_02_x1 on tab_02 (tdate);
create index tab_03_x1 on tab_03 (tdate);

create view q1 as
select * from tab_01
union all
select * from tab_02
union all
select * from tab_03
with row movement;

TIA
Joachim

[Serge Rielau]

Works as designed.
The theorem prover isn't smart enough to know that the intricacies of
month(). Would be an easy thing to detect thsi special case, but there
are lots of special cases out there.. Kind of tough to cover and the
optimizer would become to heavy.

Cheers
Serge