linux系统update语句,关于Update语句的调整(1)

本文可以任意转载或分发,但请注明作者和出处.

1.简介

本文针对通过对一个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

CORE9.2.0.6.0Production

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 (idchar(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 (idchar(10) not null,col1 number(20,2),col2 varchar2(100))

2/

表已创建。

已用时间:00: 00: 00.00

SQL>

SQL> declare

2begin

3for i in 1..1000000 loop

4insert /* +append*/ into pa values('a'||to_char(i),100,'test','0');

5if i <= 500000 then

6insert /* +append*/ into p values('a'||to_char(i),'b'||to_char(i),'test');

7else

8insert /*+append*/ into p values('d'||to_char(i),'b'||to_char(i),'test');

9end if;

10insert /* +append*/ into c values('b'||to_char(i),10,'test');

11if i <= 300000 then

12insert /* +append*/ into d values('a'||to_char(i),100,'test');

13else

14insert /* +append*/ into d values('c'||to_char(i),100,'test');

15end if;

16end loop;

17commit;

18end;

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)

/