An issue with UPDATE via UNION ALL view

[Eugene]

Summary:
----------
Updates against UNION ALL view does't do branch elimination, but
rather reads all the branches (partitions).

The case scenario(DB2 V8.1.4a ESE, AIX 5.2):
--------------------------------------------
The UNION ALL view (tv) was built on the following three sample tables
(t1,t2,t3)
CREATE TABLE T1
(I INTEGER,
S VARCHAR(10)
);
ALTER TABLE T1
ADD CONSTRAINT T1_CK CHECK
(I between 1 and 9)
ENFORCED
ENABLE QUERY OPTIMIZATION;
insert into t1 values(1,'t1-1'),(2, 't1-2'),(3,'t1-3');

CREATE TABLE T2
(I INTEGER,
S VARCHAR(10)
);
ALTER TABLE T2
ADD CONSTRAINT T1_CK CHECK
(I between 10 and 19)
ENFORCED
ENABLE QUERY OPTIMIZATION;
insert into t2 values(11,'t2-1'),(12, 't2-2'),(13,'t2-3');

CREATE TABLE T3
(I INTEGER,
S VARCHAR(10)
);
ALTER TABLE T3
ADD CONSTRAINT T1_CK CHECK
(I between 20 and 29)
ENFORCED
ENABLE QUERY OPTIMIZATION;
insert into t3 values(21,'t3-1'),(22, 't3-2'),(23,'t3-3');
create view tv as
select * from t1 union all
select * from t2 union all
select * from t3
;

db2 => select * from tv order by i,s

I S
----------- ----------
1 t1-1
2 t1-2
3 t1-3
11 t2-1
12 t2-2
13 t2-3
21 t3-1
22 t3-2
23 t3-3

9 record(s) selected.
For the statement like:

update tv set s='15'
where i=11

that tries to update data in the partition t2, the execution plan
shows no partition pruning:
Rows
RETURN
( 1)
Cost
I/O
|
1
UPDATE
( 2)
150.114
6
/---+---\
1 3
UPDATE TABLE: DB2PE
( 3) T3
125.108
5
/---+---\
1 3
UPDATE TABLE: DB2PE
( 4) T2
100.103
4
/---+---\
1 3
UNION TABLE: DB2PE
( 5) T1
75.0961
3
+----------------+----------------+
2.32831e-10 1 2.32831e-10
TBSCAN TBSCAN TBSCAN
( 6) ( 7) ( 8)
25.0322 25.0316 25.0322
1 1 1
| | |
3 3 3
TABLE: DB2PE TABLE: DB2PE TABLE: DB2PE
T1 T2 T3


Also, this statement run from db2batch indiates data access in all
those three tables:

*** Table Snapshot ***

Table Schema = DB2PE
Table Name = T3
Table File ID = 31
Table Type = USER TABLE
Rows read = 3
Rows written = 0
Accesses to overflow record = 0

*** Table Snapshot ***

Table Schema = DB2PE
Table Name = T2
Table File ID = 30
Table Type = USER TABLE
Rows read = 3
Rows written = 0
Accesses to overflow record = 0

*** Table Snapshot ***

Table Schema = DB2PE
Table Name = T1
Table File ID = 29
Table Type = USER TABLE
Rows read = 3
Rows written = 0
Accesses to overflow record = 0
Is that an expected behavior or a defect?

Thanks,
-Eugene

[Serge Rielau]

Do you have optimization level 5 or higher?

Cheers
Serge

[Eugene]

Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...

Do you have optimization level 5 or higher?

Cheers
Serge

Hi Serge,

The optimization level was 5 by defualt.

Regards,
-Eugene

[Serge Rielau]

My memory is getting hazy.... but try making "I" NOT NULL.. Just a
hunch. Check constraints accept "uncknown", thsi yoru comnstraint is not
fully partitioning.
You do have a NOT NULL where clause, but maybe DB2 doesn't figure that
one out.....

Cheers
Serge

[Eugene]

Serge,

NOT NUT specified for T[1,2,3].I doesn't help. Also I tried to change
CHECK from BETWEEN to IN list but that didn't help too.

-Eugene
Serge Rielau <sr*****@ca.ibm.com> wrote in message news:<2t*************@uni-berlin.de>...

My memory is getting hazy.... but try making "I" NOT NULL.. Just a
hunch. Check constraints accept "uncknown", thsi yoru comnstraint is not
fully partitioning.
You do have a NOT NULL where clause, but maybe DB2 doesn't figure that
one out.....

Cheers
Serge

[Matt Emmerton]

"Eugene" <eu****@profitlogic.com> wrote in message
news:95**************************@posting.google.c om...

Serge Rielau <sr*****@ca.ibm.com> wrote in message

news:<2t*************@uni-berlin.de>...

Do you have optimization level 5 or higher?

Cheers
Serge

Hi Serge,

The optimization level was 5 by defualt.

Regards,
-Eugene

Try optimization level 7. I think you need this to get the UNION ALL
optimizations.

--
Matt Emmerton

[Eugene]

I tried the optimization level 7 but it doesn't prune too.

-Eugene
"Matt Emmerton" <ma**@gsicomp.on.ca> wrote in message news:<lv********************@rogers.com>...

"Eugene" <eu****@profitlogic.com> wrote in message
news:95**************************@posting.google.c om...

Serge Rielau <sr*****@ca.ibm.com> wrote in message

news:<2t*************@uni-berlin.de>...

Do you have optimization level 5 or higher?

Cheers
Serge

Hi Serge,

The optimization level was 5 by defualt.

Regards,
-Eugene

Try optimization level 7. I think you need this to get the UNION ALL
optimizations.