It appears as if it matters how constraints are added to a table, when
it comes to optimizing a plan. Any thoughts on this:

db2level
DB21085I Instance "db2inst1" uses "32" bits and DB2 code release
"SQL09072"
with level identifier "08030107".
Informational tokens are "DB2 v9.7.0.2", "s100514", "IP23088", and Fix Pack
"2".
Product is installed at "/opt/ibm/db2/V9.7".



drop table emp;
create table emp (
emp_no int not null primary key,
title varchar(10) not null,
salary int not null,
check (emp_no > 0),
check (title in ('BOSS','WORK'))

);
alter table emp add constraint c1
check (title <> 'BOSS' or salary > 100);
alter table emp add constraint c2
check (title <> 'WORK' or salary <= 100);


insert into emp with t (n) as (values 1 union all select n+1 from t
where n+1 < 1000) select n, case when mod(n,10) = 0 then 'BOSS' else
'WORK' end, case when mod(n,10) = 0 then 110 else 0 end + mod(n,90) from t;

runstats on table ltjn.emp with distribution and detailed indexes all;

explain plan for select * from emp where emp_no = 0

Original Statement:
------------------
select *
from emp
where emp_no = 0


Optimized Statement:
-------------------
SELECT NULL AS "EMP_NO", NULL AS "TITLE", NULL AS "SALARY"
FROM (VALUES) AS Q1
WHERE (1 = 0)

Access Plan:
-----------
Total Cost: 0.000138987
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
TBSCAN
( 2)
2.3381e-05
0
TABFNC: SYSIBM
GENROW


Works nicely, db2 derives FALSE from emp_no = 0 (predicate) and emp_no >
0 (constraint)


However, if the same constraints are added as:

drop table emp2;
create table emp2 (
emp_no int not null primary key,
title varchar(10) not null,
salary int not null,
check (emp_no > 0),
check (title in ('BOSS','WORK')),
check (title <> 'BOSS' or salary > 100),
check (title <> 'WORK' or salary <= 100)
);

insert into emp2 with t (n) as (values 1 union all select n+1 from t
where n+1 < 1000) select n, case when mod(n,10) = 0 then 'BOSS' else
'WORK' end, case when mod(n,10) = 0 then 110 else 0 end + mod(n,90) from t;

runstats on table ltjn.emp2 with distribution and detailed indexes all;


Original Statement:
------------------
select *
from emp2
where emp_no = 0


Optimized Statement:
-------------------
SELECT 0 AS "EMP_NO", Q1.TITLE AS "TITLE", Q1.SALARY AS "SALARY"
FROM LTJN.EMP2 AS Q1
WHERE (Q1.EMP_NO = 0)

Access Plan:
-----------
Total Cost: 15.1386
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
FETCH
( 2)
15.1386
2
/---+----\
1 999
IXSCAN TABLE: LTJN
( 3) EMP2
7.57476 Q1
1
INDEX: SYSIBM
SQL111221095416710
Q1


Now the optimizer get confused and fails to inject (emp_no > 0) into the
query (assuming or is at stake here).

Is there a difference in how these constraints are handled internally? I
tried:

create table emp3 (
emp_no int not null primary key,
title varchar(10) not null,
salary int not null,
check (emp_no > 0),
check (title in ('BOSS','WORK')),
constraint c1 check (title <> 'BOSS' or salary > 100),
constraint c2 check (title <> 'WORK' or salary <= 100)
);

but it still fails.


Rewriting the constraints as:

create table emp4 (
emp_no int not null primary key,
title varchar(10) not null,
salary int not null,
check (emp_no > 0),
check (title in ('BOSS','WORK')),
constraint c1 check ( NOT (title = 'BOSS' AND salary <= 100)),
constraint c2 check ( NOT (title = 'WORK' AND salary > 100))
);

insert into emp4 with t (n) as (values 1 union all select n+1 from t
where n+1 < 1000) select n, case when mod(n,10) = 0 then 'BOSS' else
'WORK' end, case when mod(n,10) = 0 then 110 else 0 end + mod(n,90) from t;

runstats on table ltjn.emp4 with distribution and detailed indexes all;

is successfull:

select *
from emp4
where emp_no = 0


Optimized Statement:
-------------------
SELECT NULL AS "EMP_NO", NULL AS "TITLE", NULL AS "SALARY"
FROM (VALUES) AS Q1
WHERE (1 = 0)

Access Plan:
-----------
Total Cost: 0.000138987
Query Degree: 1

Rows
RETURN
( 1)
Cost
I/O
TBSCAN
( 2)
2.3381e-05
0
TABFNC: SYSIBM
GENROW



/Lennart