起因:
对SQL语句一直都是简单的应用,复杂的一般放到存储过程、应用程序中处理,但是由于要借用一批数据,临时用了个update语句,结果速度奇慢无比。
目标:
于是就下定决心,仔细分析。
环境:
oracle92数据库。
TEST_WORD(BIANHAO VARCHAR2(10) NOT NULL, XINGMING VARCHAR2(10))。
测试数据如下:
1000, ‘张三’;
1000-01, NULL(空白);
1001, ‘张三’;
1001-01, NULL(空白);
1002, ‘张三’;
1003-01, NULL(空白);
......大约一共有100万条数据
1000, ‘张三’;
1000-01, ‘张三’;
1001, ‘张三’;
1001-01, ‘张三’;
1002, ‘张三’;
1003-01, ‘张三’;
......最终结果SQL:
update test_word a set a.xingming=(
select xingming from test_word b
where b.xingming is not null
and substr(a.bianhao,1,4)=b.bianhao
)
update TEST_WORD a set a.XINGMING=(
select b.XINGMING from (select * from TEST_WORD where BIANHAO not like '%-01') b
where (b.BIANHAO||'-01')=a.BIANHAO
)
where exists (select XINGMING from (select * from TEST_WORD where BIANHAO not like '%-01') b
where (b.BIANHAO||'-01')=a.BIANHAO)
::成功
人品爆发后的SQL:
merge into TEST_WORD a
using (
select BIANHAO, XINGMING from TEST_WORD
where length(BIANHAO)=4
) b
on (substr(a.BIANHAO,1,4)=b.BIANHAO and length(a.BIANHAO)>4)
when MATCHED then
update set a.XINGMING=b.XINGMING
when NOT MATCHED then
insert values(b.BIANHAO, b.XINGMING)
merge语句在9i中必须既有when MATCHED then语句也必须有when NOT MATCHED then语句
但是在10g中就可以仅有when MATCHED then语句,或者仅有when NOT MATCHED then语句
感想:
中间过程其实挺纠结的。