本文可以任意转载或分发,但请注明作者和出处.
1. 简介
本文针对http://www.itpub.net/301247.html通过对一个Update语句的各种优化尝试，简要介绍了几种对update语句的调整方法，并比较了它们之间的优劣性。
1. 测试平台及数据
SQL>select *from v$version;
BANNER
----------------------------------------------------------------
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
PL/SQL Release 9.2.0.5.0 - Production
CORE 9.2.0.6.0 Production
TNS for IBM/AIX RISC System/6000: Version 9.2.0.5.0 - Production
NLSRTL Version 9.2.0.5.0 – Production
SQL>
SQL> set timing on
SQL> set autotrace off
SQL> drop table pa
2 /
表已丢弃。
已用时间: 00: 00: 01.07
SQL> drop table p
2 /
表已丢弃。
已用时间: 00: 00: 02.06
SQL> drop table c
2 /
表已丢弃。
已用时间: 00: 00: 02.03
SQL> drop table d
2 /
表已丢弃。
已用时间: 00: 00: 00.02
SQL>
SQL> create table pa (id char(10) not null,col1 number(20,2),col2 varchar2(100),flag char(1) default '0')
2 /
表已创建。
已用时间: 00: 00: 00.00
SQL> create table p (id char(10) not null,cid char(10) not null,col2 varchar2(100))
2 /
表已创建。
已用时间: 00: 00: 00.00
SQL> create table c (cid char(10) not null,col1 number(20,2),col2 varchar2(100))
2 /
表已创建。
已用时间: 00: 00: 00.00
SQL> create table d (id char(10) not null,col1 number(20,2),col2 varchar2(100))
2 /
表已创建。
已用时间: 00: 00: 00.00
SQL>
SQL> declare
2 begin
3 for i in 1..1000000 loop
4 insert /* +append*/ into pa values('a'||to_char(i),100,'test','0');
5 if i <= 500000 then
6 insert /* +append*/ into p values('a'||to_char(i),'b'||to_char(i),'test');
7 else
8 insert /*+append*/ into p values('d'||to_char(i),'b'||to_char(i),'test');
9 end if;
10 insert /* +append*/ into c values('b'||to_char(i),10,'test');
11 if i <= 300000 then
12 insert /* +append*/ into d values('a'||to_char(i),100,'test');
13 else
14 insert /* +append*/ into d values('c'||to_char(i),100,'test');
15 end if;
16 end loop;
17 commit;
18 end;
19 /
PL/SQL 过程已成功完成。
已用时间: 00: 05: 30.07
SQL> -- 创建索引
SQL> alter table pa add constraint pk_pa primary key(id)
2 /
表已更改。
已用时间: 00: 00: 11.01
SQL> alter table p add constraint pk_p primary key(id)
2 /
表已更改。
已用时间: 00: 00: 04.09
SQL> create index idx_p_cid on p(cid)
2 /
索引已创建。
已用时间: 00: 00: 07.00
SQL> alter table c add constraint pk_c primary key(cid)
2 /
表已更改。
已用时间: 00: 00: 06.06
SQL> alter table d add constraint pk_d primary key(id)
2 /
表已更改。
已用时间: 00: 00: 04.03
SQL>
SQL>
SQL> -- 分析表
SQL> analyze table pa compute statistics for table for all indexes
2 /
表已分析。
已用时间: 00: 00: 13.07
SQL> analyze table p compute statistics for table for all indexes
2 /
表已分析。
已用时间: 00: 00: 15.04
SQL> analyze table c compute statistics for table for all indexes
2 /
表已分析。
已用时间: 00: 00: 13.03
SQL> analyze table d compute statistics for table for all indexes
2 /
表已分析。
已用时间: 00: 00: 13.07
SQL>
本例中有四张表，pa、p、c、d每张表均有一百万行数据、均有主键。表的大小如下：
SQL> SELECT bytes/1024/1024 FROM user_segments WHERE segment_name = 'PA'
2 /
BYTES/1024/1024
---------------
29
SQL> SELECT bytes/1024/1024 FROM user_segments WHERE segment_name = 'P'
2 /
BYTES/1024/1024
---------------
35
SQL> SELECT bytes/1024/1024 FROM user_segments WHERE segment_name = 'C'
2 /
BYTES/1024/1024
---------------
26
SQL> SELECT bytes/1024/1024 FROM user_segments WHERE segment_name = 'D'
2 /
BYTES/1024/1024
---------------
26
待调整的update语句为：
update pa
set pa.col1=
(select pa.col1*c.col1
from p, c
where p.id=pa.id and p.cid=c.cid
)
where exists (select 1
from p,c
where p.id=pa.id and p.cid=c.cid
)
and not exists (select 1
from d
where id=pa.id)
/
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/6906/viewspace-21644/，如需转载，请注明出处，否则将追究法律责任。
