可笑的优化:LECCO Sql Expert
在Oracle数据库中创建两个表,脚本如下:
create table t_a (
id number(10,0),
num number(16,6),
constraint pk_t_a primary key (id)
)/
create table t_b (
id number(10,0),
num number(16,6),
constraint pk_t_b primary key (id)
)/
再放入一点数据,脚本如下:
insert into t_a values(1, 100);
insert into t_a values(2, 300);
insert into t_a values(3, 500);
insert into t_a values(4, 700);
insert into t_a values(5, 900);
insert into t_b values(3, 600);
insert into t_b values(4, 800);
insert into t_b values(6, 1200);
commit;
写出原始的Sql如下:
update t_a a
set num = (select num from t_b where id = a.id)
where id in (select id from t_b);
优化后的结果之一如下:
update t_a a
set num = (select num
from t_b
where id = a.id)
where EXISTS (SELECT 'X'
from t_b
WHERE id = id)
可是,优化是正确的吗?
执行原始脚本后结果是
id num
1 100
2 300
3 600
4 800
5 900
而执行优化后的脚本结果是
id num
1 (null)
2 (null)
3 600
4 800
5 (null)
可笑吧?!!!不知道Sql Expert是怎样优化的,原本想用它来改善性能,看来……
我的测试环境:Oracle 9i,Sql Expert Pro 3.3.2