 |
|
| Computers Forum Index » Computer - Databases - IBM DB2 » optimizer observations... |
|
Page 1 of 1 |
|
| Author |
Message |
| Lennart... |
 Posted: Sat Oct 17, 2009 9:31 am |
|
|
|
Guest
|
I played around a bit just for fun and observed the following. Any
thoughts anyone (LUW 9.5 fixpak 0)?
Assume a table emp and some BR:
create table emp (
empno int not null
constraint emp_pk
primary key
constraint emp_empno
check(empno > 0)
,job varchar(10) not null
constraint job_domain
check(job in ('MANAGER','CLERK','TRAINER'))
,sal int not null
constraint max_salary
check(sal > 999)
);
Now, if I ask a query like:
explain plan for select * from emp where empno <= 0
db2 does a good job and optimizes the query to:
SELECT NULL AS "EMPNO", NULL AS "ENAME", NULL AS "SEX", NULL AS
"BORN", NULL
AS "JOB", NULL AS "SAL"
FROM (VALUES) AS Q1
WHERE (1 = 0)
What's strange is that if I add another rule saying that any manager
must earn more than 900, i.e.
constraint min_manager_salary
check(job <> 'MANAGER' or sal > 900)
and ask the same question:
explain plan for select * from emp where empno <= 0
the optimizer gets confused, and fails to optimize the query:
Optimized Statement:
-------------------
SELECT Q1.EMPNO AS "EMPNO", Q1.JOB AS "JOB", Q1.SAL AS "SAL"
FROM LELLE.EMP AS Q1
WHERE (Q1.EMPNO <= 0)
Even if I set optimization level to 9 it fails to identify the
contradiction it discovered before.
If I manually rewrite the new rule to:
constraint min_manager_salary
check( not (job = 'MANAGER' and sal <= 900) )
the optimizer once again is successful.
My conclusion is to avoid disjunctions in constraints. Does anyone
know if there are any plans to improve this kind of semantic rewrites
in future versions?
/Lennart |
|
|
| Back to top |
|
|
|
|
|
All times are GMT
The time now is Sun Nov 29, 2009 11:26 am
|
|