|
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
| 
