472,353 Members | 1,246 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,353 software developers and data experts.

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

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

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;

Nov 12 '05 #1
1 2263
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.

Nov 12 '05 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

by: Buttercup | last post by:
I am having alot of trouble with a union query Im trying to create. The bizarre thing is I initially created this query and managed to save it as a...
by: Susanne Bandi | last post by:
Hello I've experienced that DB2 unfortunately does not eliminate subselects of a UnionAll-View if the predicate's content is not hardcoded but...
by: Akinia | last post by:
Hi every body I've a little problem with my query, I can't figure it out. I've three tables: Table A (EmployeNr, Date, Code) Table B...
by: SusannaW | last post by:
Hi NG I need to create a query wich shows me several fields of one record in several records - in access97. Well, here's an example: ok,...
by: Mark | last post by:
Hi all, I am currently in the design stages of a database for work. I have come up with a way to get informaion I need using a union query but was...
by: spam | last post by:
If I run the following query in Access 2002 then I get the expected result: SELECT * FROM CSVImport UNION SELECT * FROM AssetTemp; I get...
by: KoliPoki | last post by:
Hello every body. I have a small issue. Problem: I have a table with 4 descriptor columns (type). I need to formulate a query to retrieve a...
by: MR USER | last post by:
Hello, I made a union query that works perfectly in Access giving me a table that display all my nice records. when I try to retrieve these...
by: wugon.net | last post by:
question: db2 LUW V8 UNION ALL with table function month() have bad query performance Env: db2 LUW V8 + FP14 Problem : We have history data...
by: jalbright99669 | last post by:
Am having a bit of a time with URL Rewrite. I need to incorporate http to https redirect with a reverse proxy. I have the URL Rewrite rules made...
by: antdb | last post by:
Ⅰ. Advantage of AntDB: hyper-convergence + streaming processing engine In the overall architecture, a new "hyper-convergence" concept was...
by: Matthew3360 | last post by:
Hi there. I have been struggling to find out how to use a variable as my location in my header redirect function. Here is my code. ...
by: Matthew3360 | last post by:
Hi, I have a python app that i want to be able to get variables from a php page on my webserver. My python app is on my computer. How would I make it...
by: AndyPSV | last post by:
HOW CAN I CREATE AN AI with an .executable file that would suck all files in the folder and on my computerHOW CAN I CREATE AN AI with an .executable...
by: Arjunsri | last post by:
I have a Redshift database that I need to use as an import data source. I have configured the DSN connection using the server, port, database, and...
by: WisdomUfot | last post by:
It's an interesting question you've got about how Gmail hides the HTTP referrer when a link in an email is clicked. While I don't have the specific...
by: Oralloy | last post by:
Hello Folks, I am trying to hook up a CPU which I designed using SystemC to I/O pins on an FPGA. My problem (spelled failure) is with the...
by: BLUEPANDA | last post by:
At BluePanda Dev, we're passionate about building high-quality software and sharing our knowledge with the community. That's why we've created a SaaS...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.