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

(2)

1.通常的调整方法

--通常来说都先把update语句中的where子句提取出来进行调整。

A．调整前的执行过程：

SQL> select *from pa

2where exists (select 1

3from p,c

4where p.id=pa.id and p.cid=c.cid

5)

6and not exists (select 1

7from d

8where id=pa.id)

9/

已选择200000行。

已用时间: 00: 00: 36.06

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=15347 Card=2500 Byte

s=60000)

10FILTER

21TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=2500 Bytes=60

000)

31NESTED LOOPS (Cost=4 Card=1 Bytes=36)

43TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 By

tes=24)

54INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=

1)

63INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1

Bytes=12)

71INDEX (UNIQUE SCAN) OF 'PK_D' (UNIQUE) (Cost=2 Card=1 By

tes=12)

Statistics

----------------------------------------------------------

340recursive calls

0db block gets

6517005consistent gets

7physical reads

0redo size

6182538bytes sent via SQL*Net to client

546928bytes received via SQL*Net from client

13335SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

200000rows processed

B．对sql语句进行加提示调整

--鉴于用于连接的字段均存在NOT NULL约束,可以考虑使用hash_aj提示调整查询

--注意:如果d.id/pa.id没有not null约束,hash_aj将不起作用.可以加d.id is not null/pa.id is not null条件避免这种情况

SQL>

SQL> select *from pa

2where exists (select 1

3from p,c

4where p.id=pa.id and p.cid=c.cid

5)

6and id not in (select /*+hash_aj*/ id

7from d)

8/

已选择200000行。

已用时间:00: 00: 32.00

Execution Plan

----------------------------------------------------------

0SELECT STATEMENT Optimizer=CHOOSE (Cost=963 Card=1 Bytes=36)

10FILTER

21HASH JOIN (ANTI) (Cost=959 Card=1 Bytes=36)

32TABLE ACCESS (FULL) OF 'PA' (Cost=347 Card=50000 Bytes

=1200000)

42INDEX (FAST FULL SCAN) OF 'PK_D' (UNIQUE) (Cost=284 Ca

rd=1000000 Bytes=12000000)

51NESTED LOOPS (Cost=4 Card=1 Bytes=36)

65TABLE ACCESS (BY INDEX ROWID) OF 'P' (Cost=3 Card=1 By

tes=24)

76INDEX (UNIQUE SCAN) OF 'PK_P' (UNIQUE) (Cost=2 Card=

1)

85INDEX (UNIQUE SCAN) OF 'PK_C' (UNIQUE) (Cost=1 Card=1

Bytes=12)

Statistics

----------------------------------------------------------

0recursive calls

0db block gets

2906545consistent gets

3physical reads

0redo size

6275882bytes sent via SQL*Net to client

546928bytes received via SQL*Net from client

13335SQL*Net roundtrips to/from client

0sorts (memory)

0sorts (disk)

200000 rows processed

SQL>

虽然从执行时间时间上来看，不加提示和加提示分别为00: 00: 36.06和00: 00: 32.00，相差不大，但从统计信息上来看，由原来的6517005consistent gets变为2906545consistent gets，调整收到一定效果。